SQLSERVER 语句交错引发的死锁问题案例详解
作者：一线码农 发布时间：2024-01-15 04:31:24
标签：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.
要解决死锁问题，个人感觉需要非常熟知各种隔离级别，尤其是 可提交读 模式下的 CURD 加解锁过程，这一篇我们就来好好聊一聊。
二：死锁简析
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='上海' WHERE OrderID=10258
WAITFOR DELAY '00:00:10'
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='Dr.' WHERE EmployeeID=1;
WAITFOR DELAY '00:00:10'
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
三：总结
在真实的死锁案例集锦中，相对来说 语句顺序交错 引发的死锁会相对多一些，其次就是 书签查找，这个放到后面的文章中来聊，面对 语句顺序交错 的场景尽量的收集整理死锁的 xml数据，或许有很多意想不到的发现。
来源：https://www.cnblogs.com/huangxincheng/archive/2023/02/20/17136299.html
0
投稿猜你喜欢
示例matplotlib中的animation提供了动态绘图功能，下面列举一个最简单的动态绘制三角函数的例子，来初步演示一下。import- 数据库安全性问题一直是围绕着数据库管理员的恶梦，数据库数据的丢失以及数据库被非法用户的侵入使得数据库管理员身心疲惫不堪。围绕数据库的安全性问
- 目录前言1.获取当前时刻时间1.1返回当前时刻的日期和时间1.2获取当前时刻的日期1.3获取当前时刻的时间1.4获取当前时刻的周数2.日期时
- 第一种方法:****1.****打开SQL Server，写好登录名和密码点击连接.****2.****打开数据库，右键某一个数据库，选择&
- vue3 ref构建响应式变量失效问题描述在Vue3中使用ref声明响应式变量，同时用函数对值进行变化，但是无法响应式改变值<temp
- 本文实例讲述了Python实现将HTML转换成doc格式文件的方法。分享给大家供大家参考，具体如下：网页上的一些文章，因为有格式的原因，它们
- AJAX简介AJAX = Asynchronous JavaScript and XML（异步的 JavaScript 和 XML）。AJA
- error类型是go语言的一种内置类型，使用的时候不用特定去import，他本质上是一个接口 type error interface{ E
- 块级元素块级元素生成一个元素框，（默认地）它会填充其父级元素的内容，旁边不能有其他元素。换句话说，他在元素框之前和之后生成了“分隔”符。我们
- 今天学习CI框架过程中遇到个问题: A PHP Error was encountered Severity: Notice Message
- Python函数函数就是把具有独立功能的代码块封装成一个小模块,可以直接调用,从而提高代码的编写效率以及重用性, 需要注意的是, 函数需要被
- 有了Web框架和ORM框架，我们就可以开始装配App了。通常，一个Web App在运行时都需要读取配置文件，比如数据库的用户名、口令等，在不
- 本文实例讲述了Python工厂函数用法。分享给大家供大家参考，具体如下：参考了很多代码、别人的文章以及书籍，最后自己做了适合自己理解的一种理
- 没有gpu,改用cpu跑代码以为很麻烦的修改，很大动干戈的改动，以及无止尽的报错，结果，就改动代码中的device就OK了，惊不惊喜，意不意
- django的url采用正则表达式进行配置，虽然强大却也广为诟病。反对者们认为django的url配置过于繁琐，且不支持默认的路由功能。我倒
- 本文实例讲述了Python Web框架之Django框架Form组件用法。分享给大家供大家参考，具体如下：Form简介在HTTP中，表单（f
- 前言分析目标网站的登录方式目标地址： https://github.com/login 登录方式做出分析：
- 本文实例讲述了python使用any判断一个对象是否为空的方法。分享给大家供大家参考。具体实现代码如下：>>> eth =
- openpyxlopenpyxl模块属于第三方模块，是一个在 python 中能够处理 excel 文件的模块，还有比较出名的是xlrd、x
- 本文实例讲述了Python网络编程之使用TCP方式传输文件操作。分享给大家供大家参考，具体如下：TCP文件下载器客户端需求：输入要下载的文件
