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

原文: https://blog.csdn.net/qiumuxia0921/article/details/50574879

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

引言

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

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

分析：

我们来分析一下上面的图，上面为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中检测到死锁，这个时候，就会出现一个牺牲品的事情，以至于系统能够继续运行。

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

右边的Process Node2为：

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

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

模拟：

下面是我们的建表语句：

[sql]  view plain  copy

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

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

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>  

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

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

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

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

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

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

首先我运行事务一（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 在第一条记录这里等待。

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

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

解决方案：

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

结束语：

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