粘贴会有各种格式显示问题,原文链接:点击查看原文
引言
近来做省一级计算机一级考试系统的时候,学生端进行大批量判分的时候,出现了这样的问题(事务(进程 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语句的死锁。
模拟:
下面是我们的建表语句:
- <span style=“font-size:18px;”>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’)</span>
<span style="font-size:18px;">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')</span>
我们第一个update事务为:
- <span style=“font-size:18px;”>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</span>
<span style="font-size:18px;">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</span>
第二个update事务为:
- <span style=“font-size:18px;”>SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
- begin tran
- update table1
- set A=‘aa3’
- where B=‘b1’
- EXEC sp_lock @@spid
- commit tran</span>
<span style="font-size:18px;">SET TRANSACTION ISOLATION LEVEL Read UNCOMMITTED
begin tran
update table1
set A='aa3'
where B='b1'
EXEC sp_lock @@spid
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的运行原理来解决我们数据库中的死锁问题,原理很重要。