0

通过extended events来观察死锁

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under , , ,
继续前一篇http://zhongxiao37.blogspot.com/2015/04/observing-locks-by-using-extended-events.html。如果我想要观察deadlock呢？

在SSMS > management > extented events > session 下面添加一个deadlock的event session，然后直接watch live data，这个时候就可以看到deadlock的信息和示意图了。

reader - writer deadlock

这个一个比较常见的读写死锁情况。第一个session在第一个表上拥有X锁，需要第二个表的S锁；第二个session在第二个表上有X锁，需要第一个表上的S锁。这样，这两个锁互相block，互相等待，直到SQL SERVER 选择一个session为victim并kill掉。
-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
SELECT * FROM parents WHERE id = 10

-- Session 2
SELECT * FROM children WHERE id = 10


writer - writer deadlock

和前者比较相像只是S锁换成X锁而已。

-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
UPDATE parents
SET name = 2
WHERE id = 10


-- Session 2
UPDATE children
SET name = 2
WHERE id = 10


key-lookup deadlock

这个死锁就比较特殊了。一个session是拥有X锁也要访问X锁，而另一个session是拥有S锁需要访问S锁。一般来说，S锁释放得都比较快，但是在并发的情况下就很容易发生了。下面的代码都会一直跑下去，但是跑不了多久死锁就会发生了。Session 1里面，UPDATE的column是value，刚好被non-clustered index给included掉，所以，在clustered index和non-clustered index上都会有X锁。而对于session 2来说，SELECT的WHERE clause是parent_id，则会先在non-clustered index上面拥有S锁，再继续在clustered index上面request S锁。在某一时刻，两者刚好各自拥有一个锁，request另一个锁，这时死锁就发生了。

-- Session 1
DECLARE @i int = 1

while 1 = 1
BEGIN
        UPDATE children
        SET value = @i
        WHERE id = 10

        SET @i = @i + 1
END

-- Session 2
DECLARE @i int

while 1 = 1
BEGIN

        SELECT @i = name
        FROM children
        WHERE parent_id = 10

END



引用
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/



0

采花

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under
又一年春天，买了一盆玫瑰，想着栽在家里，不知道它什么时候会死去，只是希望它能够活得更久些。闲着没事在家，洒上点水，举起相机。在捏下快门的一瞬间，这个小家伙竟然跑进了我的镜头。


原图地址
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

John Huang's function for converting lock resources - ConvertedLockResource

Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under , ,
出处： http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588

在用extended events观察SQL SERVER lock行为的时候，resource_0, resource_1, resource_2 比较让人困惑，究竟是什么意思。John Huang的博客写了一个函数，方便我们去理解具体在那个object上面lock的行为。


USE [master]
GO

create function [dbo].[ConvertedLockResource](@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(60)
as
begin
        if @ResourceType = 'OBJECT'
                return cast(@res0 as varchar(20));
        else if @ResourceType in ('PAGE', 'EXTENT')
        begin
                return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
        end
        else if @ResourceType = 'RID'
        begin
                return  cast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
                                + cast(@res0 as varchar(20))+':'
                                + cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
        end
        else if @ResourceType = 'HOBT'
        begin
                return cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        else if @ResourceType = 'KEY'
        begin
                return  '('
                                + lower(convert( varchar(20),
                                                        cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
                                                        + cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
                                                ,2))
                                +')/'
                                + cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        return null
end

GO
0

Tips in OFFSET FETCH

Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under ,


出处: http://sqlmag.com/t-sql/offsetfetch-part-2

1. OFFSET FETCH 指定需要ORDER BY语句。如果只是想随便拿出100行而不需考虑排序，性能问题，可以直接ORDER BY (SELECT NULL）。但这个不是随机排序的。
2. 如果要保证是随机排序的，那么就需要GUID了，即ORDER BY(CHECKSUM(NEWID())). NEWID生成GUID，CHECKSUM保证更好的随机性。
3. 但是如果table太大，以上方法会引起table scan和sort。引入TABLESAMPLE来避免这个问题吧。

0

GetNums by using OFFSET FETCH

Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under ,
这个方法在做本地测试的时候用的比较多。直接返回指定n行数字。

出处： http://tsql.solidq.com/books/source_code/Performance.txt

CREATE FUNCTION [dbo].[GetNums](@n AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT n FROM Nums ORDER BY n OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;

GO

Usage:

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