Showing posts with label extended events. Show all posts
Showing posts with label extended events. Show all posts
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

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