sqlserver死锁详解

开启死锁监控

  • 开启监视死锁的开关，让 SQL Server 遇到死锁时，在 errorlog 文件中打印出死锁的详细信息
--.将死锁信息写入错误日志
DBCC TRACEON (1222, -1);

一个完整的死锁过程

  • 第1步，创建演示表及数据
USE demo
GO 

IF OBJECT_ID('Bookings') IS NOT NULL DROP TABLE Bookings;
IF OBJECT_ID('Payments') IS NOT NULL DROP TABLE Payments;

CREATE TABLE Bookings (ID INT PRIMARY KEY, Status VARCHAR(10), Amount DECIMAL(10,2));
CREATE TABLE Payments (ID INT PRIMARY KEY, BookingID INT, Amount DECIMAL(10,2));

INSERT INTO Bookings VALUES (1, 'Pending', 100.00);
INSERT INTO Payments VALUES (101, 1, 100.00);
  • 第2步，执行如下代码（会话 A）
-- ==================== 会话 A ====================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  UPDATE Bookings SET Status = 'Confirmed' WHERE ID = 1;
  WAITFOR DELAY '00:00:05';
  UPDATE Payments SET Amount = 95.00 WHERE BookingID = 1;
COMMIT TRANSACTION;
  • 第3步，5 秒内执行如下代码（会话 B）
-- ==================== 会话 B ====================
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
  UPDATE Payments SET Amount = 90.00 WHERE ID = 101;
  WAITFOR DELAY '00:00:05';
  UPDATE Bookings SET Amount = 95.00 WHERE ID = 1;
COMMIT TRANSACTION;
  • 第4步，等待约 5-10 秒，SQL Server 会检测到死锁，其中一个会话提交成功，另一个被终止（通常是执行成本较低的那个）并返回以下错误：

注：本例中会话 A（Process ID 63）提交成功，会话 B（Process ID 65）被终止

Msg 1205, Level 13, State 51, Line 6
Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

消息 1205，级别 13，状态 51，第 6 行
事务(进程 ID 65)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。

这就是经典的交叉资源争夺导致的死锁场景。当两个会话分别持有了对方下一步需要更新的资源锁，并且相互等待对方释放锁时，就形成了死锁。

解读：具体就是，会话 A 先锁定了 Bookings 表中的行，然后延迟5秒；会话 B 先锁定了 Payments 表中的行，也延迟5秒。之后会话 A 尝试更新 Payments 表，但该行已被会话 B 锁定，于是会话 A 进入等待状态；同时会话 B 尝试更新 Bookings 表，但该行已被会话 A 锁定，于是会话 B 也进入等待状态。此时两个会话互相等待对方释放锁，谁也无法继续执行，SQL Server 的死锁检测机制会主动选择一个会话作为“牺牲品”回滚其事务，从而打破僵局。

死锁日志分析

  • 查看最近30天发生的死锁次数
--.查看最近30天发生的死锁次数
DECLARE @start_time datetime 
SET @start_time = GETDATE()-30 
EXEC sys.xp_readerrorlog 0,1,N'deadlock victim',Null,@start_time,NULL,'DESC'

注：以上脚本的查询返回大致如下：

LogDate ProcessInfo Text
2026-05-20 09:23:44.520 spid21s deadlock victim=process22a5f245867
2026-05-20 10:46:18.850 spid22s deadlock victim=process22a5f115848
2026-05-20 15:06:15.390 spid24s deadlock victim=process22a59d5a4e8
  • 执行如下查询，获取 ERRORLOG 的完整路径
DECLARE @param nvarchar(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLDataRoot', @param OUTPUT
SELECT @param+'\Log\ERRORLOG'
  • 打开 ERRORLOG 可以看到完整的死锁记录，主要分为3部分：
--.第1部分 deadlock-list 死锁牺牲进程
2026-05-20 15:06:15.39 spid24s     deadlock-list
2026-05-20 15:06:15.39 spid24s      deadlock victim=process22a59d5a4e8

--.第2部分 process-list 进程信息, inputbuf 下面是具体执行的语句
2026-05-20 15:06:15.39 spid24s       process-list
2026-05-20 15:06:15.39 spid24s        process id=process22a59d5a4e8 taskpriority=0 logused=144 waitresource=KEY: 5:72057594047430656 (8194443284a0) waittime=2250 ownerId=104630 transactionname=user_transaction lasttranstarted=2026-05-20T15:06:08.127 XDES=0x22a6a18c428 lockMode=X schedulerid=1 kpid=7272 status=suspended spid=65 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2026-05-20T15:06:08.127 lastbatchcompleted=2026-05-20T10:46:18.860 lastattention=1900-01-01T00:00:00.860 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=WIN104 hostpid=10184 loginname=WIN104\Administrator isolationlevel=read committed (2) xactid=104630 currentdb=5 currentdbname=demo lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2026-05-20 15:06:15.39 spid24s         executionStack
2026-05-20 15:06:15.39 spid24s          frame procname=adhoc line=6 stmtstart=56 stmtend=154 sqlhandle=0x02000000015a2d170908a795af29293dfee79ff33f28e6c70000000000000000000000000000000000000000
2026-05-20 15:06:15.39 spid24s     unknown     
2026-05-20 15:06:15.39 spid24s          frame procname=adhoc line=6 stmtstart=410 stmtend=502 sqlhandle=0x02000000aab6e717678c406146f4a7363e591e2badf381350000000000000000000000000000000000000000
2026-05-20 15:06:15.39 spid24s     unknown     
2026-05-20 15:06:15.39 spid24s         inputbuf
2026-05-20 15:06:15.39 spid24s     -- ==================== 会话 B ====================
2026-05-20 15:06:15.39 spid24s     SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2026-05-20 15:06:15.39 spid24s     BEGIN TRANSACTION;
2026-05-20 15:06:15.39 spid24s       UPDATE Payments SET Amount = 90.00 WHERE ID = 101;
2026-05-20 15:06:15.39 spid24s       WAITFOR DELAY '00:00:05';
2026-05-20 15:06:15.39 spid24s       UPDATE Bookings SET Amount = 95.00 WHERE ID = 1;
2026-05-20 15:06:15.39 spid24s     COMMIT TRANSACTION;

2026-05-20 15:06:15.39 spid24s        process id=process22a5f115848 taskpriority=0 logused=144 waitresource=KEY: 5:72057594047496192 (6a526fd2904a) waittime=3514 ownerId=104628 transactionname=user_transaction lasttranstarted=2026-05-20T15:06:06.877 XDES=0x22a6a1a8428 lockMode=U schedulerid=1 kpid=5516 status=suspended spid=63 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2026-05-20T15:06:06.877 lastbatchcompleted=2026-05-20T10:46:18.857 lastattention=1900-01-01T00:00:00.857 clientapp=Microsoft SQL Server Management Studio - 查询 hostname=WIN104 hostpid=10184 loginname=WIN104\Administrator isolationlevel=read committed (2) xactid=104628 currentdb=5 currentdbname=demo lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
2026-05-20 15:06:15.39 spid24s         executionStack
2026-05-20 15:06:15.39 spid24s          frame procname=adhoc line=6 stmtstart=56 stmtend=168 sqlhandle=0x02000000d49f2010facbdd9666dcdc4b8c0982319022c3420000000000000000000000000000000000000000
2026-05-20 15:06:15.39 spid24s     unknown     
2026-05-20 15:06:15.39 spid24s          frame procname=adhoc line=6 stmtstart=418 stmtend=524 sqlhandle=0x02000000eef7790465246309b22364f331176c1eee6674160000000000000000000000000000000000000000
2026-05-20 15:06:15.39 spid24s     unknown     
2026-05-20 15:06:15.39 spid24s         inputbuf
2026-05-20 15:06:15.39 spid24s     -- ==================== 会话 A ====================
2026-05-20 15:06:15.39 spid24s     SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2026-05-20 15:06:15.39 spid24s     BEGIN TRANSACTION;
2026-05-20 15:06:15.39 spid24s       UPDATE Bookings SET Status = 'Confirmed' WHERE ID = 1;
2026-05-20 15:06:15.39 spid24s       WAITFOR DELAY '00:00:05';
2026-05-20 15:06:15.39 spid24s       UPDATE Payments SET Amount = 95.00 WHERE BookingID = 1;
2026-05-20 15:06:15.39 spid24s     COMMIT TRANSACTION;

--.第3部分 resource-list 资源信息
2026-05-20 15:06:15.39 spid24s       resource-list
2026-05-20 15:06:15.39 spid24s        keylock hobtid=72057594047430656 dbid=5 objectname=demo.dbo.Bookings indexname=PK__Bookings__3214EC27BEAFDE54 id=lock22a5ad90e00 mode=X associatedObjectId=72057594047430656
2026-05-20 15:06:15.39 spid24s         owner-list
2026-05-20 15:06:15.39 spid24s          owner id=process22a5f115848 mode=X
2026-05-20 15:06:15.39 spid24s         waiter-list
2026-05-20 15:06:15.39 spid24s          waiter id=process22a59d5a4e8 mode=X requestType=wait

2026-05-20 15:06:15.39 spid24s        keylock hobtid=72057594047496192 dbid=5 objectname=demo.dbo.Payments indexname=PK__Payments__3214EC27030A8F7F id=lock22a5ad90d80 mode=X associatedObjectId=72057594047496192
2026-05-20 15:06:15.39 spid24s         owner-list
2026-05-20 15:06:15.39 spid24s          owner id=process22a59d5a4e8 mode=X
2026-05-20 15:06:15.39 spid24s         waiter-list
2026-05-20 15:06:15.39 spid24s          waiter id=process22a5f115848 mode=U requestType=wait
  • 对以上信息进行分类展示如下：
参与者 process22a59d5a4e8 process22a5f115848
SPID spid=65（会话B） spid=63（会话A）
牺牲者 是（deadlock victim=process22a59d5a4e8） 否
进程信息\连接背景 clientapp=Microsoft SSMS - Query
hostname=WIN104
hostpid=10184
loginname=WIN104\Administrator		 clientapp=Microsoft SSMS - Query
hostname=WIN104
hostpid=10184
loginname=WIN104\Administrator
进程信息\当前数据库 currentdb=5 currentdbname=demo currentdb=5 currentdbname=demo
进程信息\正在申请的资源 waitresource=KEY: 5:72057594047430656 waitresource=KEY: 5:72057594047496192
进程信息\正在申请的锁模式 lockMode=X lockMode=U
进程信息\lockTimeout lockTimeout=4294967295 lockTimeout=4294967295
进程信息\开启了几层事务 trancount=2 trancount=2
进程信息\事务隔离级别 read committed (2) read committed (2)
进程信息\运行的语句 会话 B 中的脚本 会话 A 中的脚本
资源信息\资源类型 keylock(若为ridlock表示无聚集索引是个堆表) keylock
资源信息\具体资源 hobtid=72057594047430656
dbid=5
objectname=demo.dbo.Bookings
indexname=PK__Bookings__3214EC27BEAFDE54		 hobtid=72057594047496192
dbid=5
objectname=demo.dbo.Payments
indexname=PK__Payments__3214EC27030A8F7F
资源信息\持有资源进程/类型 process22a5f115848 (SPID 63) / Mode:X process22a59d5a4e8 (SPID 65)/ Mode:X
资源信息\等待资源进程/类型 process22a59d5a4e8 (SPID 65) / Mode:X process22a5f115848 (SPID 63) / Mode:U 
--.查询死锁报告中的资源信息
select name,database_id,compatibility_level from sys.databases where database_id = 5
select * from sys.objects where object_id in (338100245,370100359)
select * from sys.partitions where partition_id in (72057594047430656,72057594047496192)

解决死锁的方法

下面介绍几种常见的解决死锁的方法，这些方法，各有利弊，各有其使用的场合。

  • 1、优化设计，降低事务的长短和复杂度，尽量返回少的结果集

解读：返回的数据越多，需要的锁也就越多，发生死锁的概率越高。而事务越简单，就越不容易发生阻塞或死锁，尽量避免在一个事务里做很多事情。

  • 2、调整索引，以调整执行计划，减少锁的申请数目，从而降低死锁发生的概率

解读：如果死锁的双方不会申请对方要申请的锁，那么死锁也就不会发生。如果能引导SQL Server使用Seek的执行计划，只读取要返回的数据，那么申请的锁数量会大大降低，死锁的几率也会减少。推荐此法，既能提高语句执行的效率，又消除了死锁。但如果双方都要修改或返回同样的数据，那么再优化索引，可能也没有用处。

  • 3、使用“nolock”参数，让SELECT语句不要申请S锁，从而消除死锁

解读：此法解决死锁立竿见影、很受欢迎，但是它的缺点是很明显的，“nolock”意味着 SELECT 语句将能接受脏读，用户要理解它的副作用，非必要不推荐。另外，它只能解决有S锁参与的死锁问题，如果死锁发生在U锁或者X锁上，nolock是不起作用的

  • 4、升级锁的粒度，将死锁转化成一个阻塞问题

解读：死锁是因为双方都申请到了一个资源（比如同一个page上的不同RID），同时又要申请对方的资源。此时使用“PagLock” 参数直接申请Page级别的锁（只能有一个人得到锁资源），而另一个人会被阻塞住（什么资源都申请不到），此时发生的是阻塞，而不是死锁。

注意：此法虽然不会降低事务隔离级别，但是语句更容易被阻塞住，最终的并发度难免受到影响，比较“粗鲁”也不推荐。

  • 5、使用 Snapshot 隔离级别

解读：如果死锁里有S锁参与，可以把事务隔离级别从缺省的 Read Committed 改成 Snapshot（加到语句开头），对缓解此类死锁也会有很好的帮助。set transaction isolation level SNAPSHOT

  • 6、设置死锁超时参数为合理范围

解读：数据库使用锁超时来避免让用户等待时间过长，默认-1表示没有超时期限(即无限期等待)。建议 SET LOCK_TIMEOUT 300 超过300毫秒自动放弃本次操作，避免进程悬挂

Copyright © www.sqlfans.cn 2021 - 2026 All Right Reserved更新时间： 2026-09-08 14:57:00

results matching ""

    No results matching ""