SQLSERVER 语句交错引发的死锁研究

ゝ一世哀愁。 2024-03-16 12:35 62阅读 0赞

### 一：背景 ###

#### 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 操作，稍等几秒后就会出现死锁。

![0f1ba1fbda755d05eacf29e623824ea8.png][]

#### 2. 寻找死锁源头 ####

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

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

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

![30a601099b608621a12940a369ac1ba7.png][]

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

![bf09b76a29ba7d0c605636b282ac6c89.png][]

<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` 的内容就非常必要了，截图如下：

![28b25f104ae0805dbc575e01cb7d2708.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` 无锁提示是一个好办法。

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数据，或许有很多意想不到的发现。

[0f1ba1fbda755d05eacf29e623824ea8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/bf42bb9198d54365abdda00a08d4670c.png
[30a601099b608621a12940a369ac1ba7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/745f3563ac7c4f74b9358f01bee9315c.png
[bf09b76a29ba7d0c605636b282ac6c89.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/a6d28cf5bd184fe590762ffca5da12c2.png
[28b25f104ae0805dbc575e01cb7d2708.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/16/d699804589844feba01b361c2bb222c9.png