服务器之家:专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - SQLSERVER 语句交错引发的死锁问题案例详解

SQLSERVER 语句交错引发的死锁问题案例详解

2023-02-22 15:05一线码农 Sql Server

这篇文章主要介绍了SQLSERVER 语句交错引发的死锁研究,要解决死锁问题,个人感觉需要非常熟知各种隔离级别,尤其是 可提交读 模式下的 CURD 加解锁过程,这一篇我们就来好好聊一聊

一:背景

 

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.

要解决死锁问题,个人感觉需要非常熟知各种隔离级别,尤其是 可提交读 模式下的 CURD 加解锁过程,这一篇我们就来好好聊一聊。

二:死锁简析

 

1. 一个测试案例

开启两个会话 6566 ,分别使用如下查询。

  1. -- 会话 65 --
  2. BEGIN TRAN
  3. UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
  4. WAITFOR DELAY '00:00:10'
  5. SELECT * FROM dbo.Orders WHERE OrderID=10258
  6. ROLLBACK
  7.  
  8. -- 会话 66 --
  9. BEGIN TRAN
  10. UPDATE dbo.Orders SET ShipAddress='上海' WHERE OrderID=10258
  11. WAITFOR DELAY '00:00:10'
  12. SELECT * FROM dbo.Employees WHERE EmployeeID=1;
  13. ROLLBACK

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

SQLSERVER 语句交错引发的死锁问题案例详解

2. 寻找死锁源头

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

  • SQL Server Profile
  • DBCC TRACEON(1222)
  • DMV VIEW

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

SQLSERVER 语句交错引发的死锁问题案例详解

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

SQLSERVER 语句交错引发的死锁问题案例详解

  1. <deadlock-list>
  2. <deadlock victim="process2d69c9748c8">
  3. <process-list>
  4. <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">
  5. <executionStack>
  6. <frame procname="adhoc" line="5" stmtstart="24" stmtend="128" sqlhandle="0x020000007383d935b349bc173c0f104de14945e9a526322b0000000000000000000000000000000000000000">
  7. unknown </frame>
  8. <frame procname="adhoc" line="5" stmtstart="204" stmtend="294" sqlhandle="0x020000002c3b203105961d63d10b17e54ed6ac081105f9450000000000000000000000000000000000000000">
  9. unknown </frame>
  10. </executionStack>
  11. <inputbuf>
  12.  
  13. BEGIN TRAN
  14. UPDATE dbo.Orders SET ShipAddress=&apos;上海&apos; WHERE OrderID=10258
  15. WAITFOR DELAY &apos;00:00:10&apos;
  16. SELECT * FROM dbo.Employees WHERE EmployeeID=1;
  17. ROLLBACK
  18. </inputbuf>
  19. </process>
  20. <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">
  21. <executionStack>
  22. <frame procname="adhoc" line="5" stmtstart="26" stmtend="118" sqlhandle="0x02000000dd7720067e0519b8a368501716c04b4b50cfe6be0000000000000000000000000000000000000000">
  23. unknown </frame>
  24. <frame procname="adhoc" line="5" stmtstart="196" stmtend="282" sqlhandle="0x0200000093f01512208755a056f5f28930fbd3dedf58a2850000000000000000000000000000000000000000">
  25. unknown </frame>
  26. </executionStack>
  27. <inputbuf>
  28.  
  29. BEGIN TRAN
  30. UPDATE dbo.Employees SET src="/uploads/allimg/230222/150I05137-3.png" />

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

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

    3. 寻找解决方案

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

    使用 nolock 脏读

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

    1. BEGIN TRAN
    2. UPDATE dbo.Orders SET ShipAddress='上海' WHERE OrderID=10258
    3. WAITFOR DELAY '00:00:10'
    4. SELECT * FROM dbo.Employees WITH(NOLOCK) WHERE EmployeeID=1;
    5. ROLLBACK
    6.  
    7. BEGIN TRAN
    8. UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
    9. WAITFOR DELAY '00:00:10'
    10. SELECT * FROM dbo.Orders WITH(NOLOCK) WHERE OrderID=10258
    11. ROLLBACK

    使用 MVCC 多版本控制

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

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

    1. ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON

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

    1. -- 会话 65 --
    2. SET TRAN ISOLATION LEVEL SNAPSHOT
    3. BEGIN TRAN
    4. UPDATE dbo.Employees SET Title='Dr.' WHERE EmployeeID=1;
    5. WAITFOR DELAY '00:00:10'
    6. SELECT * FROM dbo.Orders WHERE OrderID=10258
    7. ROLLBACK
    8.  
    9. -- 会话 66 --
    10. SET TRAN ISOLATION LEVEL SNAPSHOT
    11. BEGIN TRAN
    12. UPDATE dbo.Orders SET ShipAddress='上海' WHERE OrderID=10258
    13. WAITFOR DELAY '00:00:10'
    14. SELECT * FROM dbo.Employees WHERE EmployeeID=1;
    15. ROLLBACK

    三:总结

    在真实的死锁案例集锦中,相对来说 语句顺序交错 引发的死锁会相对多一些,其次就是 书签查找,这个放到后面的文章中来聊,面对 语句顺序交错 的场景尽量的收集整理死锁的 xml数据,或许有很多意想不到的发现。

    到此这篇关于SQLSERVER 语句交错引发的死锁研究的文章就介绍到这了,更多相关sqlserver语句交错引发的死锁内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

    原文链接:https://www.cnblogs.com/huangxincheng/archive/2023/02/20/17136299.html

延伸 · 阅读

精彩推荐