两个事务 更新一张堆表 遇到奇怪的死锁问题

Question

说先执行如下脚本 创建数据

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[table1](
 [A] [nvarchar](10) NULL,
 [B] [nvarchar](10) NOT NULL,
 [C] [nvarchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa1', N'b1', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa2', N'b3', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b4', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b5', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b2', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b6', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b7', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa3', N'b8', N'11')
INSERT [dbo].[table1] ([A], [B], [C]) VALUES (N'aa1', N'b9', N'11')

然后创建以下三个查询

查询一：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 begin tran
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
       update table1
     set A='aa1'
     where B='b3'
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
    EXEC sp_lock @@spid
 
   waitfor  delay '00:00:10'

   update table1
     set A='aa2'
     where B='b8'
     EXEC sp_lock @@spid
    print convert(nvarchar(30),convert(datetime,getdate(),121),121)
 commit tran

查询二：

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
    begin tran
  update table1 
    set A='aa3'
    where B='b7'
    EXEC sp_lock @@spid
   commit tran

查询三：

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
    begin tran
  update table1 
    set A='aa3'
    where B='b1'
 
    EXEC sp_lock @@spid
   commit tran

当我执行完查询一再再马上执行查询二没有问题，执行查询一马上执行查询三就会死锁，

查询二和三的区别就是他们更新的记录，一个在查询一的B='b3'记录前一个在其后，真不知道为什么会出现这种情况，谢谢！

Answer 1

需要锁定的记录

查询一: B='b3', B='b8'
查询二: B='b7'
查询三: B='b1'

如果只是查询一与查询二, 或者查询一与查询三, 理论上似乎应该不会有死锁, 死锁是要形成循环的

以查询一与查询三为例

如果查询一要的资源被查询三锁定, 查询三会等待查询三完成, 查询三只锁了一条记录, 既然已经锁定, 那么不存在需要再等等查询一释放资源的问题, 不应该形成死锁;

如果查询三要的资源被查询一锁定, 那么它也只会等, 而它只锁定一条记录, 既然它在等等获取锁资源, 说明它没有锁定资源, 查询一也不存在等待查询三的问题, 所以也不应该形成死锁

当然, 不排除其他资源的锁定导致 ( 我测试了一下是没有死锁 )

Answer 2

可以通过 Profile, 跟踪 Locks -- Deadlock grapp 事件来跟踪死锁具体冲突的资源

在查询上, 查询三会因为死锁而终止, 看不到锁的信息, 你可以另外开一个查询窗口, 查询 sp_lock 了解锁的信息, 再结合查询一输出的两个锁信息进行死锁分析

Answer 3

对于两种情况的监控如下图，请帮忙看一下吧

有死锁：（查询一，查询三）

无死锁（查询一，查询二）

Answer 4

What you posted don't tell anything. You can enable deadlock trace flag 1204 then run queries and get deadlock details in sql server log.

Answer 5

锁定的资源不同，你用request_session_id看一下两次执行之后产生的Lock。

---

Please Mark As Answer if it is helpful.

Answer 6

我在上面的回复里有截图，我用的是sqlserver profile跟踪出来的，高手能帮我看一下吗？

Answer 7

Did you add deadlock event in profiler to trace? Can't find any in your post.

Answer 8

dead lock case： trace pic

no dead lock case： trace pic

dead lock log： 

91885 2014-05-27 10:41:52.110 spid17s  deadlock victim=processa2fc78
91886 2014-05-27 10:41:52.120 spid17s   process-list
91887 2014-05-27 10:41:52.120 spid17s    process id=processa2fc78 taskpriority=0 logused=236 waitresource=RID: 31:1:14949:3 waittime=5953 ownerId=487279423 transactionname=user_transaction lasttranstarted=2014-05-27T10:41:46.163 XDES=0x1a311590 lockMode=U schedulerid=2 kpid=5676 status=suspended spid=75 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-05-27T10:41:46.163 lastbatchcompleted=2014-05-27T10:40:38.320 clientapp=Microsoft SQL Server Management Studio - Query hostname=SHAWODC005 hostpid=2560 loginname=sasalesbudget isolationlevel=read uncommitted (1) xactid=487279423 currentdb=31 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
91888 2014-05-27 10:41:52.120 spid17s     executionStack
91889 2014-05-27 10:41:52.120 spid17s      frame procname=adhoc line=3 stmtstart=70 sqlhandle=0x020000004675d30037997477d290c76f5c39390354f3bb69
91890 2014-05-27 10:41:52.120 spid17s UPDATE [table1] set [A] = @1  WHERE [B]=@2    
91891 2014-05-27 10:41:52.120 spid17s      frame procname=adhoc line=3 stmtstart=142 stmtend=252 sqlhandle=0x02000000a9ebc827447a50cfec38acf7a358bc00772168d2
91892 2014-05-27 10:41:52.120 spid17s update table1 
91893 2014-05-27 10:41:52.120 spid17s     set A='aa3'
91894 2014-05-27 10:41:52.120 spid17s     where B='b4'    
91895 2014-05-27 10:41:52.120 spid17s     inputbuf
91896 2014-05-27 10:41:52.120 spid17s  SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
91897 2014-05-27 10:41:52.120 spid17s     begin tran
91898 2014-05-27 10:41:52.120 spid17s     update table1 
91899 2014-05-27 10:41:52.120 spid17s     set A='aa3'
91900 2014-05-27 10:41:52.120 spid17s     where B='b4'
91901 2014-05-27 10:41:52.120 spid17s     EXEC sp_lock @@spid
91902 2014-05-27 10:41:52.120 spid17s     commit tran
91903 2014-05-27 10:41:52.120 spid17s    process id=processa5e4aa8 taskpriority=0 logused=244 waitresource=RID: 31:1:14949:2 waittime=2790 ownerId=487279348 transactionname=user_transaction lasttranstarted=2014-05-27T10:41:44.327 XDES=0x3f036280 lockMode=U schedulerid=1 kpid=7224 status=suspended spid=56 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-05-27T10:41:44.327 lastbatchcompleted=2014-05-27T09:21:53.153 clientapp=Microsoft SQL Server Management Studio - Query hostname=SHAWODC005 hostpid=2560 loginname=sasalesbudget isolationlevel=read committed (2) xactid=487279348 currentdb=31 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
91904 2014-05-27 10:41:52.120 spid17s     executionStack
91905 2014-05-27 10:41:52.120 spid17s      frame procname=adhoc line=9 stmtstart=70 sqlhandle=0x020000004675d30037997477d290c76f5c39390354f3bb69
91906 2014-05-27 10:41:52.120 spid17s UPDATE [table1] set [A] = @1  WHERE [B]=@2    
91907 2014-05-27 10:41:52.120 spid17s      frame procname=adhoc line=9 stmtstart=496 stmtend=608 sqlhandle=0x0200000097ea19022b2d8a1ad169b5a244512207404d2202
91908 2014-05-27 10:41:52.120 spid17s update table1
91909 2014-05-27 10:41:52.120 spid17s      set A='aa2'
91910 2014-05-27 10:41:52.120 spid17s      where B='b6'    
91911 2014-05-27 10:41:52.120 spid17s     inputbuf
91912 2014-05-27 10:41:52.120 spid17s  begin tran
91913 2014-05-27 10:41:52.120 spid17s     print convert(nvarchar(30),convert(datetime,getdate(),121),121)
91914 2014-05-27 10:41:52.120 spid17s        update table1
91915 2014-05-27 10:41:52.120 spid17s      set A='aa1'
91916 2014-05-27 10:41:52.120 spid17s      where B='b5'
91917 2014-05-27 10:41:52.120 spid17s     print convert(nvarchar(30),convert(datetime,getdate(),121),121)
91918 2014-05-27 10:41:52.120 spid17s      waitfor  delay '00:00:5'
91919 2014-05-27 10:41:52.120 spid17s    update table1
91920 2014-05-27 10:41:52.120 spid17s      set A='aa2'
91921 2014-05-27 10:41:52.120 spid17s      where B='b6'
91922 2014-05-27 10:41:52.120 spid17s     print convert(nvarchar(30),convert(datetime,getdate(),121),121)
91923 2014-05-27 10:41:52.120 spid17s  commit tran   
91924 2014-05-27 10:41:52.120 spid17s   resource-list
91925 2014-05-27 10:41:52.120 spid17s    ridlock fileid=1 pageid=14949 dbid=31 objectname=Training_Test.dbo.table1 id=lock16f0c9c0 mode=X associatedObjectId=72057594089766912
91926 2014-05-27 10:41:52.120 spid17s     owner-list
91927 2014-05-27 10:41:52.120 spid17s      owner id=processa5e4aa8 mode=X
91928 2014-05-27 10:41:52.120 spid17s     waiter-list
91929 2014-05-27 10:41:52.120 spid17s      waiter id=processa2fc78 mode=U requestType=wait
91930 2014-05-27 10:41:52.120 spid17s    ridlock fileid=1 pageid=14949 dbid=31 objectname=Training_Test.dbo.table1 id=lock24050840 mode=X associatedObjectId=72057594089766912
91931 2014-05-27 10:41:52.120 spid17s     owner-list
91932 2014-05-27 10:41:52.120 spid17s      owner id=processa2fc78 mode=X
91933 2014-05-27 10:41:52.120 spid17s     waiter-list
91934 2014-05-27 10:41:52.120 spid17s      waiter id=processa5e4aa8 mode=U requestType=wait 

What is required？ thank you

Answer 9

Deadlock happened between spid 56 and 75, both ran 'UPDATE [table1] set [A] = @1  WHERE [B]=@2' in transaction that means each session will lock related data and index pages for whole transaction.

Answer 10

As for the query2：

SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
    begin tran
  update table1 
    set A='aa3'
    where B='b7'
    EXEC sp_lock @@spid
   commit tran

use “where B='b7'” to replace  “where B='b1'”,the deadlock will not happen. it's why?

in fact,I wang to know the  differences between the query2  and query3 in above，thank you

Answer 11

Sql has to get exclusive lock for updating so read uncommitted isolation level will not work here. Any reason to update rows in transaction? If required by business rule, try add lock hint in the query to lock related rows only.

Answer 12

thank you for your rewards. I want to know the reason that  the sql "update table1  set A='aa3' where B='b1'"  cause a deadlock,but the sql "update table1  set A='aa3' where B='b7'" can normal run the two transactions.It is so strange  me.

ps: there is not  the primary key in the table.

Looking forward to your suggestions，thank you.

Answer 13

Any index in the table? Sql uses page lock by default, may cause deadlock if multiple sessions are waiting on locks on same pages.

Answer 14

您好，

請參考:Using a Clustered Index to Solve a SQL Server Deadlock Issue

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 15

通过Trace 发现了原因， 通过 Profile 跟踪锁的加锁（Lock:Acquired） 和释放锁(Lock:Released ) 这两个事件可以发现， 更新录的时候，会对扫描的每条记录都会有更新锁 （U） 的加锁和释放锁的操作

了解了这个过程， 那么对于死锁就很好解释：
 对于两个查询而言， 查询一的第一个更新扫描所有记录，扫描过程会对扫描的每一条记录下U锁， 如果满足更新条件，则转化为X锁更新；如果不满足更新条件，则释放U锁。更新一完成后，第一个更新的记录保持X锁（因为事务没有完成），查询一等待第二个更新操作

 对于查询二的更新，与查询一的更新过程相同，如果更新的记录在查询一第一个更新的记录前，那么查询二所更新的记录也会持有X锁，但在扫描记录进行到查询一条一个更新的记录的时候，需要等待查询一完成（已经有X锁的记录无法下U锁），这个时候查询二被查询一Block
对于查询一， 第二个更新进行时，它也扫描所有记录，进行到更新二所在的记录的时候，它无法取得U锁（因为已经被查询二下了X锁）， 这个时候查询一等待查询二完成。 在这种情况下，查询一和查询二就是互相等待了，符合死锁条件

如果查询二更新的记录在查询一第一个更新的记录之后，那么查询二的U扫描行到查询一第一次更新记录的时候，就会因为锁冲突导致无法进行下去，必须等待查询一完成， 这个时候查询二没有会导致查询二第一个更新无法进行的锁， 也就不会导致死锁了

Answer 16

之前的分析是对更新过程的理解不够， 没有测出死锁，与数据有关，检查RID发现， 在我的电脑上，页中数据的顺序与插入的顺序有差异

Answer 17

谢谢你的回复，很同意你的解释。

之前看过您的文章，关于sqlserver的 page信息。

向您学习！