SQL Server事务执行过程中中释放锁导致的死锁问题 - 排查与分析 ...

0. 前情提要

系统的某个用来上报数据的接口存在死锁的问题。这个接口内部对多张表进行了Update操作，执行顺序为A表、B表、C表、D表、A表。死锁发生的SQL，一条是第一次更新A表的SQL，另一条是第二次更新A表的SQL。整个更新都处在一个事务内，理论上讲，只要第一个Session开始执行事务，第二个Session就会由于无法获取到A表的锁而被阻塞，直到第一个Session执行完毕释放锁，那为什么对A表的更新还会产生死锁呢？
1. 准备工作

用准备来做测试的表

1. SET ANSI_NULLS ON
2. GO
3. SET QUOTED_IDENTIFIER ON
4. GO
5. CREATE TABLE [dbo].[Test](
6.         [Id] [int] NOT NULL,
7.         [Progress] [int] NOT NULL,
8.         [Score] [int] NOT NULL,
9.         [Grade] [int] NOT NULL,
10.         [Usn] [int] NOT NULL,
11.         [ProjectId] [int] NOT NULL,
12.         [IsDeleted] [int] NOT NULL,
13. CONSTRAINT [PK_Id] PRIMARY KEY CLUSTERED
14. (
15.         [Id] ASC
16. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
17. ) ON [PRIMARY]
18. GO
19. CREATE UNIQUE NONCLUSTERED INDEX [index_usn_and_project_id] ON [dbo].[Test]
20. (
21.         [Usn] ASC,
22.         [ProjectId] ASC
23. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
24. GO

复制代码

插入一条测试用的数据

1. PRINT @@spid
2. insert into dbo.Test (Id, Progress, Score, Grade, Usn, ProjectId, IsDeleted)
3. VALUES (1, 1, 1, 1, 1, 1, 1);

复制代码

2. 非聚簇索引的情况下，在表上执行一次update的加锁过程

用来测试的update语句

1. BEGIN TRAN
3. PRINT @@spid
4. update dbo.Test
5. set Progress=2
6. WHERE Usn=1 and ProjectId=1
8. COMMIT TRAN

复制代码

利用Sql Server Profiler追踪加锁与释放锁的过程

从图中我们可以得知，在锁增长阶段，存在以下加锁步骤：

• 根据输入条件，找到非聚簇索引所在页，并获取非聚簇索引所在页的IU锁
  
• 获取非聚簇索引的U锁
  
• 根据非聚簇索引的值，找到聚簇索引（即主键索引）所在的页，获取页的IU锁
  
• 获取聚簇索引的U锁
  
• 将聚簇索引所在页的锁提升为IX锁
  
• 将聚簇索引的锁提升为X锁
  
• 更新数据
  
• 释放非聚簇索引所在页的IU锁
  
• 释放非聚簇索引的U锁
  
• 释放聚簇索引的X锁
  
• 释放聚簇索引所在页的IX锁
  

所有的锁都被争取的申请与释放
2. 非聚簇索引的情况下，在表上执行两个update的加锁

1. BEGIN TRAN
3. PRINT @@spid
4. update dbo.Test
5. set Progress=2
6. WHERE Usn=1 and ProjectId=1
8. update dbo.Test
9. set Score=2,Grade = 2
10. WHERE Usn=1 and ProjectId=1
12. COMMIT TRAN

复制代码

从红框中可以看到，非聚簇索引索引在第一个update时申请U锁、释放U锁，并在第二个update时再次申请U锁、释放U锁。
在这里，有意思的事情就发生了：第一个update在聚簇索引上申请的X锁，在第一个update执行完成后，并没有像非聚簇索引一样被释放。
这里猜测一个原因：由于非聚簇索引并不需要更新，它只是用来更新聚簇索引数据的一个跳板，因此在聚簇索引更新完后就被释放了。而聚簇索引上的X锁，则需要在事务commit之后才会被释放。
为了验证这一点，尝试在第一次update时，同时更新ProjectId

1. BEGIN TRAN
3. PRINT @@spid
4. update dbo.Test
5. set Progress=2,ProjectId=2
6. WHERE Usn=1 and ProjectId=1
8. update dbo.Test
9. set Score=2,Grade = 2
10. WHERE Usn=1 and ProjectId=2
12. COMMIT TRAN

复制代码

红线上方的就是第一个update， 由于对非聚簇索引进行了修改，所以非聚簇索引页申请了X锁，且没有在第一次Update语句结束后释放锁。由此验证了上面的假设
3. 推测与模拟

由上面的内容，我们知道了一件事情：在Update时，会申请非聚簇索引上的U锁，并且会在Update结束后（而不是事务提交后）释放锁；当在一个事务内存在两个Update（且更新的是同一条数据）时，会在非聚簇索引上存在 获取锁 -> 释放锁 -> 获取锁 -> 释放锁 的过程。
同时，我们也知道，假如同时存在两个Session要Update同一张表的同一条数据，那么晚到的Session由于事务的原因，会等待第一个Session释放非聚簇索引上的锁（因为Update的入口就是获取非聚簇索引的锁）。
那么，我们就能推理出一种情况：Session1执行完了第一个Update，并将自己持有的非聚簇索引的U锁释放，保留着聚簇索引的X锁；Session2看到非聚簇索引的U锁被释放，于是获取了U锁；此时Session1试图再次获取非聚簇索引的U锁，但非聚簇索引的U锁已经被Session2占有。而Session2要进一步更新数据，就需要Session1手里的聚簇索引的X锁。于是，死锁就发生了

接下来就是验证：左侧的sql先执行，在第一个update后，延迟1分钟；左侧sql开始之后，执行右侧的sql。理论上讲，此时左右会触发死锁：

最终的结果，成功触发死锁，右侧的sql作为后来者被牺牲：

本次运行的加锁、释放锁的过程，其中白色部分为左侧sql的执行过程（spid=53），蓝色的部分是右侧的sql的执行过程（spid=57），下面以53和57代替：

可以看到，53释放了非聚簇索引后，57立刻获取了非聚簇索引上的U锁，并获得了聚簇索引所在页的IU锁，但由于53任然持有聚簇索引的X锁，导致57无法进一步申请聚簇索引的锁：

而53这时试图获取非聚簇索引所在页上的IU锁（被57持有），由此引发了死锁。经过一段时间后，数据库发现死锁的存在，并主动终结了57，让57持有的资源被释放，从而保证53可以顺利执行
4. 结论

到这里，本次死锁发生的原因其实已经明确了，原因可以归结为，在一个事务内存在对某一资源的重复申请与释放，在并发场景下，就可能会因为对该资源的争夺而产生死锁。在开发的过程中，应当尽量避免在并发情况下，对同一资源的重复申请与释放

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！