共计 5739 个字符，预计需要花费 15 分钟才能阅读完成。
这篇文章主要讲解了“SQLSERVER 语句交错引发的死锁问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“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 --
BEGIN TRAN
UPDATE dbo.Employees SET Title= Dr. WHERE EmployeeID=1;
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK
-- 会话 66 --
BEGIN TRAN
UPDATE dbo.Orders SET ShipAddress= 上海 WHERE OrderID=10258
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK两个会话非常简单，交错的对 Employees 和 Orders 进行 SELECT 和 UPDATE 操作，稍等几秒后就会出现死锁。
2. 寻找死锁源头
当我们的应用程序拿到了这样的输出其实作用是不大的，要想溯源最好就是通过不断的对 SQLSERVER 进行监视来捕获死锁时的上下文信息，手段也有很多：
SQL Server Profile
DBCC TRACEON(1222)
DMV VIEW
这里我们就用第一种方式，一定要勾选 TextData 项，因为这里面会有死锁上下文信息的 xml 表示，截图如下：
将 profile 开启后，重新执行刚才的两个查询，一旦出现死锁，profile 就会成功捕获，然后 copy 出 TextData 项，截图如下：
deadlock-list
deadlock victim= process2d69c9748c8
process-list
process id= 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= Microsoft SQL Server Management Studio - Query hostname= DESKTOP-STS8TPB hostpid= 1696 loginname= DESKTOP-STS8TPB\Administrator isolationlevel= read committed (2) xactid= 70740 currentdb= 7 currentdbname= Northwind lockTimeout= 4294967295 clientoption1= 671090784 clientoption2= 390200
executionStack
frame procname= adhoc line= 5 stmtstart= 24 stmtend= 128 sqlhandle= 0x020000007383d935b349bc173c0f104de14945e9a526322b0000000000000000000000000000000000000000
unknown /frame
frame procname= adhoc line= 5 stmtstart= 204 stmtend= 294 sqlhandle= 0x020000002c3b203105961d63d10b17e54ed6ac081105f9450000000000000000000000000000000000000000
unknown /frame
/executionStack
inputbuf
BEGIN TRAN
UPDATE dbo.Orders SET ShipAddress= apos; 上海 apos; WHERE OrderID=10258
WAITFOR DELAY apos;00:00:10 apos;
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK
/inputbuf
/process
process id= 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= Microsoft SQL Server Management Studio - Query hostname= DESKTOP-STS8TPB hostpid= 1696 loginname= DESKTOP-STS8TPB\Administrator isolationlevel= read committed (2) xactid= 70716 currentdb= 7 currentdbname= Northwind lockTimeout= 4294967295 clientoption1= 671090784 clientoption2= 390200
executionStack
frame procname= adhoc line= 5 stmtstart= 26 stmtend= 118 sqlhandle= 0x02000000dd7720067e0519b8a368501716c04b4b50cfe6be0000000000000000000000000000000000000000
unknown /frame
frame procname= adhoc line= 5 stmtstart= 196 stmtend= 282 sqlhandle= 0x0200000093f01512208755a056f5f28930fbd3dedf58a2850000000000000000000000000000000000000000
unknown /frame
/executionStack
inputbuf
BEGIN TRAN
UPDATE dbo.Employees SET Title= apos;Dr. apos; WHERE EmployeeID=1;
WAITFOR DELAY apos;00:00:10 apos;
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK
/inputbuf
/process
/process-list
resource-list
keylock hobtid= 72057594043170816 dbid= 7 objectname= Northwind.dbo.Employees indexname= PK_Employees id= lock2d69ccbbb80 mode= X associatedObjectId= 72057594043170816
owner-list
owner id= process2d6ae694ca8 mode= X /
/owner-list
waiter-list
waiter id= process2d69c9748c8 mode= S requestType= wait /
/waiter-list
/keylock
keylock hobtid= 72057594044088320 dbid= 7 objectname= Northwind.dbo.Orders indexname= PK_Orders id= lock2d69ccbbf80 mode= X associatedObjectId= 72057594044088320
owner-list
owner id= process2d69c9748c8 mode= X /
/owner-list
waiter-list
waiter id= 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 无锁提示是一个好办法。
BEGIN TRAN
UPDATE dbo.Orders SET ShipAddress= 上海 WHERE OrderID=10258
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Employees WITH(NOLOCK) WHERE EmployeeID=1;
ROLLBACK
BEGIN TRAN
UPDATE dbo.Employees SET Title= Dr. WHERE EmployeeID=1;
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Orders WITH(NOLOCK) WHERE OrderID=10258
ROLLBACK使用 MVCC 多版本控制
现代化的关系型数据库都支持 快照读 来解决 并发读写 的冲突，同时又能保证不脏读，简而言之就是在事务修改时将修改前的数据存到 tempdb 中来形成字段的版本化。
首先需要从 数据库 级别开启它。
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON然后在各自事务中显式使用 SNAPSHOT 隔离级别查询，参考 sql 如下：
-- 会话 65 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE dbo.Employees SET Title= Dr. WHERE EmployeeID=1;
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Orders WHERE OrderID=10258
ROLLBACK
-- 会话 66 --
SET TRAN ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
UPDATE dbo.Orders SET ShipAddress= 上海 WHERE OrderID=10258
WAITFOR DELAY 00:00:10
SELECT * FROM dbo.Employees WHERE EmployeeID=1;
ROLLBACK感谢各位的阅读，以上就是“SQLSERVER 语句交错引发的死锁问题怎么解决”的内容了，经过本文的学习后，相信大家对 SQLSERVER 语句交错引发的死锁问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！
