526互联

动手分析SQL Server中死锁形成原因

发布时间 2023-09-28 21:18:51作者: hkant

摘要

上次在《动手分析SQL Server中的事务中使用的锁》一文中分析了事务中是如何使用锁，对于insert、update和select中使用的锁以实例的方式进行了初步分析，不过日常使用的时候都是很多事务同时执行，有时候难免会遇到死锁和阻塞的问题，近期在生产环境就遇到了一些死锁方面的问题，本文计划从锁的角度复现死锁过程，以期对于死锁形成机理有深入理解。

序

本文属于对于基础知识的实践应用，需要将锁的共享、互斥等应用于实际的场景分析。本文计划模拟生产环境中死锁场景，以锁的角度分析死锁的实际发生过程及相应的解决方案。

死锁的形成条件是比较明确的，需要满足四个方面的要求：

  1. 不可剥夺：锁只能由持有者释放，不能被其他进程强行夺取；
  2. 互斥性：锁定的资源是独占的，任意时间只有一个持有者；
  3. 循环等待：不同持有者之间相互依赖对方持有的锁；
  4. 请求和保持：申请新的资源的时候仍然占有之前的资源；

场景再现

准备工作

首先生成模拟数据表：

USE [Test]
GO
/****** Object:  Table [dbo].[billdetail]    Script Date: 2023/6/29 21:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[billdetail](
	[id] [varchar](36) NOT NULL,
	[billid] [varchar](36) NULL,--用于与BillInfo表的id进行关联
	[createtime] [datetime] NULL,
	[lastmodifytime] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [pk_billdetail]    Script Date: 2023/6/29 21:13:30 ******/
CREATE CLUSTERED INDEX [pk_billdetail] ON [dbo].[billdetail]
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[BillInfo]    Script Date: 2023/6/29 21:13:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BillInfo](
	[id] [varchar](36) NOT NULL,
	[code] [varchar](256) NULL,
	[begintime] [datetime] NULL,
	[endtime] [datetime] NULL,
	[createtime] [datetime] NULL,
	[lastmodifytime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [idx_billdetail_billid]    Script Date: 2023/6/29 21:13:30 ******/
CREATE NONCLUSTERED INDEX [idx_billdetail_billid] ON [dbo].[billdetail]
(
	[billid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

插入模拟数据：

insert into BillInfo values('BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8','123654789',GETDATE(),GETDATE(),GETDATE(),GETDATE())
insert into billdetail values (newid(),'BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8',GETDATE(),GETDATE())

事务详情

生产环境遇到的死锁问题有两种类型，一种是两个事务之间直接死锁，另一种是这两个事务通过第三个事务形成了死锁。下面用模式的表结构和数据来重现这两种死锁场景。

事务一的流程：

  1. 通过主键更新BillInfo字段；
  2. 根据BillId字段删除BillDetail数据；
  3. 插入BillDetail的新数据；

以上文的模拟表为基础，事务一的SQL为：

begin tran

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

delete from billdetail where billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

insert into billdetail values (newid(),'BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8',GETDATE(),GETDATE())

--rollback tran

事务二的流程：

  1. 根据BillId字段更新BillDetail数据；
  2. 根据主键更新BillInfo字段；
begin tran

update billdetail set lastmodifytime=getdate() where  billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

--rollback tran

事务三的流程：

  1. 根据主键更新BillInfo字段；
begin tran

update BillInfo set endtime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8' and code not in ('1','2')

--rollback tran

事务一执行过程中使用的锁统计表（会话编号65代表事务一，下文会使用相同会话编号）：

会话编号 被锁定的资源类型 请求的状态 请求类型 资源描述 对象名称
65 DATABASE GRANT S Test
65 PAGE GRANT IX 1:280 BillInfo
65 PAGE GRANT IX 1:464 billdetail
65 PAGE GRANT IX 1:472 billdetail
65 KEY GRANT X (2b7e3a26dcce) billdetail
65 KEY GRANT X (94b3c27cd2da) billdetail
65 KEY GRANT X (abd4d799dd7d) BillInfo
65 KEY GRANT X (d2bdbf05a1de) billdetail
65 KEY GRANT X (6d70475fafca) billdetail
65 OBJECT GRANT IX BillInfo
65 OBJECT GRANT IX billdetail

简单汇总下事务一使用的锁：

  1. 数据库Test上加的共享锁（资源类型为Database）
  2. BillInfo、BillDetail这个表上加的意向排他锁（资源类型为object）
  3. BillInfo、BillDetail表数据页上的意向排他锁（资源类型为page）
  4. BillInfo、BillDetail索引上的排他锁（资源类型为key）

事务二和事务三语句申请的资源及其锁类型也是类似的。上表中出现的锁使用模式有三种：共享（S）、意向排他（IX）和排他（X），其中不同事务之间S和IX都是可以有条件地共享数据，唯有X模式为不可共享的类型，所有在相同数据上使用排他锁的事务都必须串行执行，而由于事务一中使用了多个数据的排他锁，因此如果有其他事务使用锁的顺序与事务一不一致，就很容易出现死锁的问题。

两事务死锁过程

两事务死锁的复现步骤（用两个SSMS查询窗口分别按照如下顺序执行）：

步骤一：事务一开启事务并执行更新BillInfo的语句

begin tran

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤二：事务二开启事务并执行BillDetail的更新语句

begin tran

update billdetail set lastmodifytime=getdate() where  billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤三：事务一继续执行删除BillDetail的语句

delete from billdetail where billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤四：事务二继续执行更新BillInfo的语句

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

此时查看数据库中存在的锁（会话65为事务一，会话55为事务二）

会话编号 被锁定的资源类型 请求的状态 请求类型 资源描述 对象名称
55 DATABASE GRANT S Test
55 PAGE GRANT IX 1:280 BillInfo
55 PAGE GRANT IX 1:464 billdetail
55 KEY WAIT X (abd4d799dd7d) BillInfo
55 KEY GRANT X (d2bdbf05a1de) billdetail
55 KEY GRANT U (6d70475fafca) billdetail
55 PAGE GRANT IU 1:472 billdetail
55 OBJECT GRANT IX BillInfo
55 OBJECT GRANT IX billdetail
65 OBJECT GRANT IX billdetail
65 KEY GRANT X (abd4d799dd7d) BillInfo
65 KEY WAIT U (6d70475fafca) billdetail
65 OBJECT GRANT IX BillInfo
65 PAGE GRANT IU 1:472 billdetail
65 PAGE GRANT IX 1:280 BillInfo
65 DATABASE GRANT S Test
66 DATABASE GRANT S Test

通过这个表可以很清楚的看出来事务一在等待资源6d70475fafca上增加U锁，该资源被事务二持有；而事务二在等待资源abd4d799dd7d上增加U锁，而该资源被事务一持有。

过了几秒钟后数据库自动检测发现存在死锁进行了处理，事务一被强制中止，事务二得以继续执行。

三事务死锁过程

三事务死锁的复现步骤（用三个SSMS查询窗口分别按照如下顺序执行）：

步骤一：事务一开启事务并执行更新BillInfo的语句

begin tran

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤二：事务三执行BillInfo的更新语句


update BillInfo set endtime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8' and code not in ('1','2')

步骤三：事务二开启事务并执行BillDetail的更新语句

begin tran

update billdetail set lastmodifytime=getdate() where  billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤四：事务一继续执行删除BillDetail的语句

delete from billdetail where billid='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

步骤五：事务二继续执行更新BillInfo的语句

update BillInfo set lastmodifytime=GETDATE() where id ='BBAE2B81-7C0A-44A5-8DF5-93DF9B3E40A8'

此时查看数据库中存在的锁（会话65为事务一，会话55为事务二，会话70为事务三）

会话编号 被锁定的资源类型 请求的状态 请求类型 资源描述 对象名称
55 DATABASE GRANT S Test
55 PAGE GRANT IX 1:280 BillInfo
55 PAGE GRANT IX 1:464 billdetail
55 PAGE GRANT IU 1:472 billdetail
55 KEY WAIT X (abd4d799dd7d) BillInfo
55 KEY GRANT X (d2bdbf05a1de) billdetail
55 KEY GRANT U (6d70475fafca) billdetail
55 OBJECT GRANT IX BillInfo
55 OBJECT GRANT IX billdetail
65 OBJECT GRANT IX billdetail
65 OBJECT GRANT IX BillInfo
65 KEY WAIT U (6d70475fafca) billdetail
65 PAGE GRANT IU 1:472 billdetail
65 KEY GRANT X (abd4d799dd7d) BillInfo
65 PAGE GRANT IX 1:280 BillInfo
65 DATABASE GRANT S Test
70 DATABASE GRANT S Test
70 PAGE GRANT IU 1:280 BillInfo
70 KEY WAIT U (abd4d799dd7d) BillInfo
70 OBJECT GRANT IX BillInfo

通过这个表可以看出事务一等待资源6d70475fafca，该资源被事务二占用，而事务二在等待资源abd4d799dd7d，该资源被事务一持有，事务三等待的资源是abd4d799dd7d，同样也是被事务一持有。

由于执行时序的问题，虽然事务二和事务三在等待相同的资源，但是事务二时序排在事务三的后面，因此三个事务形成了循环等待的死锁状态。

三事务死锁示意图

死锁的形成过程分析起来很复杂，但是解决起来并不复杂，只要让事务一和事务二修改数据表的顺序保持统一，对于相同数据资源的使用就只会排队进行而不会形成死锁。

总结

数据库中死锁的形成原因有四个必要条件，由于数据库需要满足事务性的要求，不可避免地会使用各种锁控制资源地并发使用，如果事务中对于不同资源地使用顺序不一致，那么不同地事务之间就很容易因为对于资源的循环依赖而形成死锁，解决死锁地最好办法就是SQL语句使用的资源无论在什么事务中都保持相同的使用顺序。

参考文献