SQLSERVER语句交错引发的死锁问题怎么解决

2025-03-02 技术教程

这篇文章主要讲解了“SQLSERVER语句交错引发的死锁问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“SQLSERVER语句交错引发的死锁问题怎么解决”吧！

一：背景1. 讲故事

相信大家在使用 SQLSERVER 的过程中经常会遇到 阻塞 和 死锁，尤其是 死锁，比如下面的输出：

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

二：死锁简析1. 一个测试案例

开启两个会话 65 和 66 ，分别使用如下查询。

--会话65--BEGINTRANUPDATEdbo.EmployeesSETTitle='Dr.'WHEREEmployeeID=1;WAITFORDELAY'00:00:10'SELECT*FROMdbo.OrdersWHEREOrderID=10258ROLLBACK--会话66--BEGINTRANUPDATEdbo.OrdersSETShipAddress='上海'WHEREOrderID=10258WAITFORDELAY'00:00:10'SELECT*FROMdbo.EmployeesWHEREEmployeeID=1;ROLLBACK

两个会话非常简单，交错的对 Employees 和 Orders 进行 SELECT 和 UPDATE 操作，稍等几秒后就会出现死锁。

2. 寻找死锁源头

当我们的应用程序拿到了这样的输出其实作用是不大的，要想溯源最好就是通过不断的对 SQLSERVER 进行监视来捕获死锁时的上下文信息，手段也有很多：

SQL Server Profile

DBCC TRACEON(1222)

DMV VIEW

这里我们就用第一种方式，一定要勾选 TextData 项，因为这里面会有死锁上下文信息的xml表示，截图如下：

将 profile 开启后，重新执行刚才的两个查询，一旦出现死锁，profile 就会成功捕获，然后 copy 出 TextData 项，截图如下：

<deadlock-list><deadlockvictim="process2d69c9748c8"><process-list><processid="process2d69c9748c8"taskpriority="0"logused="324"waitresource="KEY:7:72057594043170816(8194443284a0)"waittime="1304"ownerId="70740"transactionname="user_transaction"lasttranstarted="2023-02-19T22:11:26.413"XDES="0x2d6a0200428"lockMode="S"schedulerid="5"kpid="13816"status="suspended"spid="66"sbid="0"ecid="0"priority="0"trancount="1"lastbatchstarted="2023-02-19T22:11:26.413"lastbatchcompleted="2023-02-19T22:11:26.410"lastattention="1900-01-01T00:00:00.410"clientapp="MicrosoftSQLServerManagementStudio-Query"hostname="DESKTOP-STS8TPB"hostpid="1696"loginname="DESKTOP-STS8TPB\Administrator"isolationlevel="readcommitted(2)"xactid="70740"currentdb="7"currentdbname="Northwind"lockTimeout="4294967295"clientoption1="671090784"clientoption2="390200"><executionStack><frameprocname="adhoc"line="5"stmtstart="24"stmtend="128"sqlhandle="0x020000007383d935b349bc173c0f104de14945e9a526322b0000000000000000000000000000000000000000">unknown</frame><frameprocname="adhoc"line="5"stmtstart="204"stmtend="294"sqlhandle="0x020000002c3b203105961d63d10b17e54ed6ac081105f9450000000000000000000000000000000000000000">unknown</frame></executionStack><inputbuf>BEGINTRANUPDATEdbo.OrdersSETShipAddress='上海'WHEREOrderID=10258WAITFORDELAY'00:00:10'SELECT*FROMdbo.EmployeesWHEREEmployeeID=1;ROLLBACK</inputbuf></process><processid="process2d6ae694ca8"taskpriority="0"logused="368"waitresource="KEY:7:72057594044088320(59ce0997f9b8)"waittime="3468"ownerId="70716"transactionname="user_transaction"lasttranstarted="2023-02-19T22:11:24.247"XDES="0x2d6a7284428"lockMode="S"schedulerid="9"kpid="7124"status="suspended"spid="65"sbid="0"ecid="0"priority="0"trancount="1"lastbatchstarted="2023-02-19T22:11:24.247"lastbatchcompleted="2023-02-19T22:11:24.247"lastattention="1900-01-01T00:00:00.247"clientapp="MicrosoftSQLServerManagementStudio-Query"hostname="DESKTOP-STS8TPB"hostpid="1696"loginname="DESKTOP-STS8TPB\Administrator"isolationlevel="readcommitted(2)"xactid="70716"currentdb="7"currentdbname="Northwind"lockTimeout="4294967295"clientoption1="671090784"clientoption2="390200"><executionStack><frameprocname="adhoc"line="5"stmtstart="26"stmtend="118"sqlhandle="0x02000000dd7720067e0519b8a368501716c04b4b50cfe6be0000000000000000000000000000000000000000">unknown</frame><frameprocname="adhoc"line="5"stmtstart="196"stmtend="282"sqlhandle="0x0200000093f01512208755a056f5f28930fbd3dedf58a2850000000000000000000000000000000000000000">unknown</frame></executionStack><inputbuf>BEGINTRANUPDATEdbo.EmployeesSETTitle='Dr.'WHEREEmployeeID=1;WAITFORDELAY'00:00:10'SELECT*FROMdbo.OrdersWHEREOrderID=10258ROLLBACK</inputbuf></process></process-list><resource-list><keylockhobtid="72057594043170816"dbid="7"objectname="Northwind.dbo.Employees"indexname="PK_Employees"id="lock2d69ccbbb80"mode="X"associatedObjectId="72057594043170816"><owner-list><ownerid="process2d6ae694ca8"mode="X"/></owner-list><waiter-list><waiterid="process2d69c9748c8"mode="S"requestType="wait"/></waiter-list></keylock><keylockhobtid="72057594044088320"dbid="7"objectname="Northwind.dbo.Orders"indexname="PK_Orders"id="lock2d69ccbbf80"mode="X"associatedObjectId="72057594044088320"><owner-list><ownerid="process2d69c9748c8"mode="X"/></owner-list><waiter-list><waiterid="process2d6ae694ca8"mode="S"requestType="wait"/></waiter-list></keylock></resource-list></deadlock></deadlock-list>

虽然上面有图形化表示，但在生产环境下参考价值并不多，因为这张图蕴含的信息比较少，熟读和整理 xml 的内容就非常必要了，截图如下：

仔细观察上面的这张图可以清晰的看到，spid=66 持有了 Orders.PK_Orders 索引上哈希码为 59ce0997f9b8 键值的 X 锁，之后需要再次获取 Employees.PK_Employees 索引上哈希码为 8194443284a0 键值上的 S 锁，很不巧的是，此时的 Employees.PK_Employees 索引上哈希码为 8194443284a0 的键值已经被 spid=65 的会话附加了 X 锁，这是一种典型的相互等待造成的死锁。

同时也可以观察到，我们的语句是一个 adhoc 即时查询，其外层也没有 存储过程 之类的包围语句。

3. 寻找解决方案

知道了是什么语句和什么语句之间的冲突之后，后面的问题就比较简单了，常见措施如下：

使用 nolock 脏读

由于冲突中涉及到了 S 锁，其实绝大多数系统对脏读不是特别敏感，所以使用 nolock 无锁提示是一个好办法。

BEGINTRANUPDATEdbo.OrdersSETShipAddress='上海'WHEREOrderID=10258WAITFORDELAY'00:00:10'SELECT*FROMdbo.EmployeesWITH(NOLOCK)WHEREEmployeeID=1;ROLLBACKBEGINTRANUPDATEdbo.EmployeesSETTitle='Dr.'WHEREEmployeeID=1;WAITFORDELAY'00:00:10'SELECT*FROMdbo.OrdersWITH(NOLOCK)WHEREOrderID=10258ROLLBACK

使用 MVCC 多版本控制

现代化的关系型数据库都支持 快照读 来解决 并发读写 的冲突，同时又能保证不脏读，简而言之就是在事务修改时将修改前的数据存到 tempdb 中来形成字段的版本化。

首先需要从 数据库 级别开启它。

ALTERDATABASENorthwindSETALLOW_SNAPSHOT_ISOLATIONON

然后在各自事务中显式使用 SNAPSHOT 隔离级别查询，参考sql如下：

--会话65--SETTRANISOLATIONLEVELSNAPSHOTBEGINTRANUPDATEdbo.EmployeesSETTitle='Dr.'WHEREEmployeeID=1;WAITFORDELAY'00:00:10'SELECT*FROMdbo.OrdersWHEREOrderID=10258ROLLBACK--会话66--SETTRANISOLATIONLEVELSNAPSHOTBEGINTRANUPDATEdbo.OrdersSETShipAddress='上海'WHEREOrderID=10258WAITFORDELAY'00:00:10'SELECT*FROMdbo.EmployeesWHEREEmployeeID=1;ROLLBACK

感谢各位的阅读，以上就是“SQLSERVER语句交错引发的死锁问题怎么解决”的内容了，经过本文的学习后，相信大家对SQLSERVER语句交错引发的死锁问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！