SQL两个事务update同一张表出现的死锁问题(waitfor delay)

抄录网址：https://blog.csdn.net/qiumuxia0921/article/details/50574879

下面是我们的建表语句：

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事务为：

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='b3'
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事务为：

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

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

我们首先看一下现在table1表中的数据，我们把数据放到Excel中进行分析：

 　　

Process Node1 :

　　首先我运行事务一（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:

　　这个时候，我们发现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锁的时候，都不是整张表进行扫描的，而是直接就可以根据索引找到这条记录进行升级锁，所以，不会出现上面的死锁的问题。