0

通过extended events来观察锁

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under ,
SQL SERVER 2008增加了这么一个新功能，叫EXTENDED EVENTS。非常非常地牛逼。我就用它来看看增删查改里面lock的behavior吧！

首先，创建测试表
USE [event_service]
GO

CREATE TABLE [dbo].[parents](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_parents] PRIMARY KEY CLUSTERED
(
        [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [event_service]
GO

CREATE TABLE [dbo].[children](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [parent_id] [bigint] NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_children] PRIMARY KEY CLUSTERED
(
        [id] ASC
)

GO

ALTER TABLE [dbo].[children]  WITH CHECK ADD  CONSTRAINT [fk_children_parent_id] FOREIGN KEY([parent_id])
REFERENCES [dbo].[parents] ([id])
GO

ALTER TABLE [dbo].[children] CHECK CONSTRAINT [fk_children_parent_id]
GO

CREATE NONCLUSTERED INDEX [ix_children_parent_id] ON [dbo].[children]
(
        [parent_id] ASC
)
INCLUDE ([value])

INSERT INTO parents
SELECT n, n, n
FROM [dbo].[GetNums](100000)

INSERT INTO children
SELECT n, n, n, n
FROM [dbo].[GetNums](100000)

然后创建 event session。对于SQL SERVER 2012来说，可以通过UI界面来创建，这里为了方便直接通过TSQL来创建了。

CREATE EVENT SESSION [sql_locks_observer] ON SERVER
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([database_id]=(5) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.lock_released(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([package0].[equal_uint64]([database_id],(5)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB)
GO

验证lock的query
; WITH locks AS (
SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'ring_buffer' AND xes.name = 'sql_locks_observer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

UNION ALL

SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xes.name = 'sql_locks_observer' AND xest.target_name = 'ring_buffer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_released"]') AS T(objlocks)
)
SELECT *
FROM locks
--ORDER BY dt

好了，准备工作完毕，可以开始了。

SELECT中的lock

select name
from children
WHERE parent_id = 10






运行那段验证lock的query，可以看到


根据http://zhongxiao37.blogspot.com/2015/04/john-huangs-function-for-converting.html 里面提到的function，我们可以查到在non-clustered index上面有一个S lock，但是clustered index上面并没有Slock。按道理来说，在这两个index上面都应该有一个S lock。http://mashijie.blogspot.com/2012/11/observing-locking-behaviour-with.html 解释到，如果如果该page上面的数据距离上次访问以后并没有改动的话，就不需要加S lock，已减少额外的lock开销。

SELECT dbo.[ConvertedLockResource]('KEY',137,3591962880,1194138335)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.children')

DBCC IND ('event_service', 'dbo.children', 2)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,590080,3)
DBCC TRACEOFF(3604)


UPDATE

用同样的方式，我们可以看UPDATE中的lock。在index和clustered index（其实就是数据页）上都有一个X lock。
UPDATE children
SET value = 13
WHERE id = 10



SELECT dbo.[ConvertedLockResource]('KEY',134,3976528128,2637997192)

DBCC IND ('event_service', 'dbo.children', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,581150,3)
DBCC TRACEOFF(3604)


INSERT

这个时候会在children表的两个index上面有个X lock，同时在parent表上面有个S lock，即需要看parent表中是否有id为1000001的记录。验证一下，的确能够看到S lock是在第1000001条记录上。
INSERT children VALUES
(1000001, 1000001, 1000001, 1000001)




SELECT dbo.[ConvertedLockResource]('KEY',133,2650734848,1539397256)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.parents')

DBCC IND ('event_service', 'dbo.parents', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,585103,3)
DBCC TRACEOFF(3604)


DELETE

在clustered index和non-clustered index上面拥有X lock。
DELETE FROM children
where parent_id = 10









引用:
https://sqlscope.wordpress.com/2012/09/16/are-key-and-row-level-locks-always-acquired/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/
http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588







0
Responses to ... 通过extended events来观察锁

Post a Comment