SQLSERVER 語句交錯引發的死鎖研究

来源:https://www.cnblogs.com/huangxincheng/archive/2023/02/20/17136299.html
-Advertisement-
Play Games

一：背景 1. 講故事 相信大家在使用 SQLSERVER 的過程中經常會遇到 阻塞 和 死鎖，尤其是 死鎖，比如下麵的輸出： (1 row affected) Msg 1205, Level 13, State 51, Line 5 Transaction (Process ID 62) was ...


一：背景

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=&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

三：總結

在真實的死鎖案例集錦中，相對來說 語句順序交錯 引發的死鎖會相對多一些，其次就是 書簽查找，這個放到後面的文章中來聊，面對 語句順序交錯 的場景儘量的收集整理死鎖的 xml數據，或許有很多意想不到的發現。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Windows——加域報錯“此工作站和主域間的信任關係失敗”
    此工作站和主域間的信任關係失敗，這個問題提示對於採用域環境管理公司電腦的朋友來說並不 陌生吧，域環境的用戶也會經常遇到的。下麵就來介紹一下解決辦法。 當域用戶輸入用戶名和密碼後顯示如下界面，現在想馬上進入系統有個辦法： 拔掉網線，重新輸入密碼，登錄系統後插上網線。這是最好的解決辦法，但是經常這樣也 ...
  • 一份隨筆讓你瞭解這個基於Raspberry Pi / 樹莓派而設計的工業電腦
    CM4 Sensing是一款基於Raspberry Pi / 樹莓派 計算模塊4（簡稱CM4），由 EDATEC 為物聯網和數據採集應用而設計的工業電腦。它充分利用了CM4的結構靈活性，解決了CPU、無線模塊和PMU的散熱問題。外置天線提高了無線通信的可靠性。 ...
  • 獲取樹形數據的全路徑
    一、簡介 • SQLite是一個輕型的資料庫，無需任何服務，無需任何配置的綠色資料庫。 • SQLite是一個進程內庫，它實現了一個自包含、無伺服器、零配置、事務性SQL資料庫引擎。SQLite的代碼在公共域中，因此可以免費用於任何目的，無論是商業還是私有。 二、安裝包 • 官方下載地址 • 此處下 ...
  • MongoDB和Elasticsearch的各使用場景對比
    MongoDB vs Elasticsearch | | MongoDB | ElasticSearch | 備註 | | | | | | | 定位 | (文檔型)資料庫 | (文檔型)搜索引擎 | 一個管理數據,一個檢索數據 | | 資源占用 | 一般 | 高 | mongo使用c++, es使用 ...
  • sql中exists 和 in的對比
    EXISTS語法解析 EXISTS 運算符用於判斷查詢子句是否有記錄，如果有一條或多條記錄存在返回 True，否則返回 False。 比如說下邊的語法，子查詢中的column_name可以和主查詢中的列名字不一樣，因為子查詢只返回yes or no。 subquery 是一個受限的 SELECT 語 ...
  • 數倉線上運維：如何進行線上增刪CN？
    摘要：集群運行過程中，根據集群的綜合負載和業務接入情況進行分析：增加CN可以適當降低CPU消耗，增大接入連接數，分散CN節點業務壓力，根據實際情況來識別是否要增加CN,如果是提升集群容量和擴展比能力，建議進行擴容操作。 本文分享自華為雲社區《【玩轉PB級數倉GaussDB(DWS)】線上運維-線上增 ...
  • 袋鼠雲高教行業數字化轉型方案，推進數字化技術和學校教育教學深度融合
    在當前的數字化轉型浪潮下，“基礎設施、配套設備、應用探索”的數字校園1.0階段即將步入尾聲、亦或已經完結，不同地區和類型的高校通過各類信息化系統和基礎設施已經初步實現了業務數字化，整個數字校園的信息基礎設施底座已有一定基礎、信息時代教育治理新模式正在逐步呈現、信息技術支持科研創新初見成效。 接下來， ...
  • Docker中Mysql容器忘記密碼的處理方法
    今天非常的倒霉，因為學習了Vue的相關知識，想自己寫一個後端伺服器來練習一下Vue 然後 忘記了Docker中Mysql的密碼。。。 很抽象 下麵是我的解決方法 一、如果在本地的Navicat Premium上連接過資料庫，就可以使用工具導出連接 二、用編輯器打開導出的鏈接文件 就可以看到 用戶名、 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類