两个事物 update同一张表出现的死锁问题（转载）

叁歲伎倆 2022-05-28 12:11 231阅读 0赞

粘贴会有各种格式显示问题，原文链接：[点击查看原文][Link 1]

# 引言 #

近来做省一级计算机一级考试系统的时候，学生端进行大批量判分的时候，出现了这样的问题（事务(进程 ID 262)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。）：

![Center][]

这个就是我们在代码中写了大批量的update语句，用trace Profiler ，我们对死锁追踪是这样的：

![Center 1][]

# 分析：    #

我们来分析一下上面的图，上面为DeakLock graph，图中左右两边的椭圆形相当于一个处理节点（Process Node），当鼠标移动到上面的时候，可以看到内部执行的代码，如update，Insert，Delete等等，有打叉的左边的椭圆形就是牺牲者，没有打叉的为优胜者。中间两个长方形就是一个资源节点（Resource Node），描述数据库中的对象，如一个表、一行或者一个索引。在我们当前的实例中，描述的是：假设左边的椭圆形为Process Node1，右边的椭圆形为Process Node2，上面的长方形为Resource Node1，下面为Resource Node2，Process Node1对Resource Node1申请一个U锁，但是，Resource Node1被Process Node2的X锁占有；另一边，Process Node2对Resource Node2申请一个U锁，但是Resource Node2被Process Node2的X锁占有。这样就形成了一个资源占有的死循环，这个时候，sql server会在sq\_lock中检测到死锁，这个时候，就会出现一个牺牲品的事情，以至于系统能够继续运行。

我们可以看一下，两个事务分为什么：

![Center 2][]

右边的Process Node2为：

![Center 3][]

可以粗略的看到，是两个update语句出现了死锁的问题。

那么为什么两条update语句会出现死锁的问题呢？我们通过一个简单的数据库进行模拟一下当时两条update语句的死锁。

# 模拟：    #

下面是我们的建表语句：

**\[sql\]** view plain copy

print ?

1. <span style=“font-size:18px;”>SET ANSI\_NULLS ON  
2. GO  
3. SET QUOTED\_IDENTIFIER ON  
4. GO  
5. CREATE TABLE \[dbo\].\[table1\](  
6.  \[A\] \[nvarchar\](10) NULL,  
7.  \[B\] \[nvarchar\](10) NOT NULL,  
8.  \[C\] \[nvarchar\](10) NULL  
9. ) ON \[PRIMARY\]  
10. GO  
11. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa1’, N‘b1’, N‘11’)  
12. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa2’, N‘b3’, N‘11’)  
13. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b4’, N‘11’)  
14. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b5’, N‘11’)  
15. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b2’, N‘11’)  
16. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b6’, N‘11’)  
17. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b7’, N‘11’)  
18. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa3’, N‘b8’, N‘11’)  
19. INSERT \[dbo\].\[table1\] (\[A\], \[B\], \[C\]) VALUES (N‘aa1’, N‘b9’, N‘11’)

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')

我们第一个update事务为：

**\[sql\]** view plain copy

print ?

1. <span style=“font-size:18px;”>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
2.  begin tran  
3.     print convert(nvarchar(30),convert(datetime,getdate(),121),121)  
4.        update t\_table  
5.      set A=‘aa1’  
6.      where B=‘b6’  
7.     print convert(nvarchar(30),convert(datetime,getdate(),121),121)  
8.     EXEC sp\_lock @@spid  
9.    
10.    waitfor  delay ’00:00:05’  
11.   
12.    update t\_table  
13.      set A=‘aa2’  
14.      where B=‘b8’  
15.      EXEC sp\_lock @@spid  
16.     print convert(nvarchar(30),convert(datetime,getdate(),121),121)  
17.  commit tran

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 begin tran
 print convert(nvarchar(30),convert(datetime,getdate(),121),121)
 update t_table
 set A='aa1'
 where B='b6'
 print convert(nvarchar(30),convert(datetime,getdate(),121),121)
 EXEC sp_lock @@spid
 
 waitfor delay '00:00:05'
 
 update t_table
 set A='aa2'
 where B='b8'
 EXEC sp_lock @@spid
 print convert(nvarchar(30),convert(datetime,getdate(),121),121)
 commit tran

第二个update事务为：

**\[sql\]** view plain copy

print ?

1. <span style=“font-size:18px;”>SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED  
2.     begin tran  
3.   update table1   
4.     set A=‘aa3’  
5.     where B=‘b1’  
6.    
7.     EXEC sp\_lock @@spid  
8.    commit tran

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

两个事物首先我们触发事务一，然后紧接着，我们触发事务二，这个时候，消息中出现：

![Image 1][]

这个时候，我们追踪死锁的时候，是这样的：

![Center 4][]

两个Process Node节点执行的事务为：

![Center 5][]

![Center 6][]

可以看到，其实是我们上面写的事务，一个是牺牲品，另外一个为优胜品。那么我们现在最大的疑问，就是，他们的申请U锁和X锁，是如何形成死锁的呢？

我们首先看一下现在table1表中的数据：

![Center 7][]

我们把数据放到Excel中进行分析：

![Center 8][]

首先我运行事务一（Process Node1），我们看看它的代码：首先要进行这一步：update table1  set A=’aa1’ where B=’b3’ ，系统运行这一步的时候，是从第一条数据开始加上U锁的，当检查到第二条数据的时候，U锁发现，符合B=’b3’时，将U锁升级为X锁，这个时候，我们就在第一条蓝线这里表示为X，接下来，继续对第3条记录进行U锁，然后为第4条，第5条，到了第11条的时候，又存在了符合B=’b3’时，将U锁升级为X锁，也就是我表示的第二条蓝线，继续加U锁，发现，整张表都已经所扫描完了，没有存在符合条件的了，这个时候，如果Process Node1到这里就运行完了，我们应该释放X锁，但是Process Node1，后面还有代码：waitfor  delay ‘00:00:10’  ，也就是等待了10秒钟，这个时候，只要事务不执行完成，X锁不会释放。

而此时，我已经运行了事务二（Process Node2），我们看到它的代码是： update table1  set A=’aa3’  where B=’b1’，通过上面说明，我们同样分析一下该update语句的执行过程，Process Node2将table1中的数据从第一条开始加U锁，这个时候，第一条数据就符合 B=’b1’，这个时候，U锁升级为X锁，继续往下执行的时候，发现第二条数据已经被Process  Node1的X锁占有，X锁为排它锁的原因为不与其他锁兼容，也就是说，不能加U锁，这个时候，Process Node2只能等待Process Node1将X锁释放，而Process Node1 没有执行完成，是不会释放X锁的，所以Process Node2 等待Process Node1释放第二条记录的X锁。

这个时候，我们发现Process Node1中waitfor  delay ‘00:00:10’ ，代码已经运行完成，之后，进行update table1 set A=’aa2’ where B=’b8’代码，这条语句从第一条数据开始加U锁，但是，我们发现第一条数据已经被Process Node2的X锁占用，也就是说，Process Node1需要等待Process Node2 的X锁释放才行，所以Process Node1 在第一条记录这里等待。

我们最后看到的应该是这样的：

![Center 9][]

因此出现了上面的现象，就是Process Node2 想要对Resource Node1（第二条记录）请求U锁，但是，Resource Node1 被Process Node1 的X锁占用，而Process Node2 想要对Resource Node2（第一条记录）请求U锁，但是Resource Node2被Process Node2 的X锁占用。

# 解决方案： #

我们的解决方案是这样的，在B的字段加上非聚集索引，就可以了，为什么呢？应为聚集索引和非聚集索引加U锁的时候，都不是整张表进行扫描的，而是直接就可以根据索引找到这条记录进行升级锁，所以，不会出现上面的死锁的问题。

# 结束语： #

对于数据库锁的问题，我们要懂得锁的运行原理。不要仅仅停留在表面上，有时候，我们需要动手模拟sql server的运行原理来解决我们数据库中的死锁问题，原理很重要。

[Link 1]: https://blog.csdn.net/qiumuxia0921/article/details/50574879
[Center]: /images/20220528/6a468254b813438b8303babc26cf15b8.png
[Center 1]: /images/20220528/feb231feaee542d4bfdbeaef89d1b0fd.png
[Center 2]: /images/20220528/f295ae3f849147eb932bee24f7aa605d.png
[Center 3]: /images/20220528/5ba59583176d4f96ae133ce88d8b288d.png
[Image 1]: 
[Center 4]: /images/20220528/3172472eb6354f2d883fb56f49cc0245.png
[Center 5]: /images/20220528/6e668776bd6146e4b8199398ae629b14.png
[Center 6]: /images/20220528/ba0d80741323471591bcc08d58bc0099.png
[Center 7]: /images/20220528/7f29c370b0ac4133a04b43240ca32a7c.png
[Center 8]: /images/20220528/0eff354c86e74be89e78cc117975b90a.png
[Center 9]: /images/20220528/fb0767ba400741f4be93f2ae5fdb3d9a.png