首页 > 代码库 > 两个事物 update同一张表出现的死锁问题 (转载)

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

引言

 

近来做省一级计算机一级考试系统的时候,学生端进行大批量判分的时候,出现了这样的问题(事务(进程 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 Node1的X锁占有。这样就形成了一个资源占有的死循环,这个时候,sql server会在sq_lock中检测到死锁,这个时候,就会出现一个牺牲品的事情,以至于系统能够继续运行。

 

我们可以看一下,两个事务分为什么:

技术分享

 

右边的Process Node2为:

技术分享

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

 

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

 

模拟:


下面是我们的建表语句:

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 (Naa1, Nb1, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa2, Nb3, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb4, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb5, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb2, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb6, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb7, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb8, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa1, Nb9, N11)INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa1, Nb1, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa2, Nb3, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb4, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb5, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb2, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb6, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb7, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa3, Nb8, N11)  INSERT [dbo].[table1] ([A], [B], [C]) VALUES (Naa1, Nb9, N11)

 

我们第一个update事务为:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDbegin tran     print convert(nvarchar(30),convert(datetime,getdate(),121),121)       update t_table       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 t_table       set A=aa2       where B=b8       EXEC sp_lock @@spid     print convert(nvarchar(30),convert(datetime,getdate(),121),121)  commit tran

第二个update事务为:

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

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

 

技术分享

这个时候,我们追踪死锁的时候,是这样的:

技术分享

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

 

 

原文链接

 

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