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

Bertha 。 2022-05-28 12:23 213阅读 0赞

# 原文: [/images/20220528/6994f5ad788e42babd26d6f5ec4428d4.png][https_blog.csdn.net_qiumuxia0921_article_details_50574879] #

**多个事务同时更新表(无论一张还是多张)都要注意u锁和x锁！**

# 引言 #

近来做省一级计算机一级考试系统的时候，学生端进行大批量判分的时候，出现了这样的问题（事务(进程 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][view plain]

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

我们第一个update事务为：

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

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</span>

第二个update事务为：

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

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</span>

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

![https_blog.csdn.net_qiumuxia0921_article_details_50574879][]

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

![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的运行原理来解决我们数据库中的死锁问题，原理很重要。

[https_blog.csdn.net_qiumuxia0921_article_details_50574879]: /images/20220528/6994f5ad788e42babd26d6f5ec4428d4.png
[Center]: /images/20220528/36ab15e31f5a4ba4b7bbd7e8410247cf.png
[Center 1]: /images/20220528/8f252f86a90440dfae170ee599725cba.png
[Center 2]: /images/20220528/ccaa5b61afbf49409e9e5c167a0a0e2d.png
[Center 3]: /images/20220528/7c99ce32f6114a3c864c0b618884e6c1.png
[view plain]: /images/20220528/6994f5ad788e42babd26d6f5ec4428d4.png#
[Center 4]: /images/20220528/2a6bd042cdfb480daea8115d1dc17c1e.png
[Center 5]: /images/20220528/aa84a87caf1d4abfbb6cf387ca4eee47.png
[Center 6]: /images/20220528/78ab84fb58b34865ab611aea9bb6f112.png
[Center 7]: /images/20220528/7b020dbbb50c46ada32111ea56f25195.png
[Center 8]: /images/20220528/8ded67e5e4c14374abb95fe3f4b71d46.png
[Center 9]: /images/20220528/e76a5ccf4d58464e87108b02529d0af7.png