萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 死鎖檢測與處理完整指南:從原理到實戰代碼

什麼是 SQL Server 死鎖?

死鎖 是指兩個或多個進程在執行過程中,因爭奪資源而造成的互相等待現象。在 SQL Server 中,當進程 A 持有資源 1 並請求資源 2,而進程 B 持有資源 2 並請求資源 1 時,雙方將陷入永久阻塞狀態。SQL Server 會自動檢測死鎖並選擇終止其中一個事務(犧牲者)以打破僵局。

死鎖檢測機制

SQL Server 內置死鎖監控器,定期掃描鎖管理器中的等待圖,尋找循環等待鏈。一旦發現死鎖,系統會根據死鎖優先級和執行成本選擇犧牲者,返回錯誤碼 1205 並回滾該事務。

實戰演示:構建死鎖場景

步驟 1:創建測試表與數據

-- 商品分類表
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'goods_sort' AND xtype = 'U')
    DROP TABLE dbo.goods_sort;

CREATE TABLE dbo.goods_sort (
    iSortID INT NOT NULL CONSTRAINT PK_iSortID PRIMARY KEY IDENTITY(1001, 1),
    sSortName NVARCHAR(20) NOT NULL
);
GO

INSERT INTO dbo.goods_sort VALUES ('服飾'), ('女包'), ('鞋子'), ('首飾'), ('美容');
GO

-- 商品表
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'goods' AND xtype = 'U')
    DROP TABLE dbo.goods;

CREATE TABLE dbo.goods (
    iID INT NOT NULL CONSTRAINT PK_iID PRIMARY KEY IDENTITY(1, 1),
    iGoodsID VARCHAR(20) NOT NULL,
    sGoodsName NVARCHAR(100) NOT NULL,
    iGoodTotal INT NOT NULL CONSTRAINT DF_iGoodTotal DEFAULT (0),
    iPrice INT NOT NULL CONSTRAINT DF_iPrice DEFAULT (0),
    iPriceTotal INT NOT NULL,
    iSortID INT NOT NULL,
    tAddDate SMALLDATETIME NOT NULL CONSTRAINT DF_tAddDate DEFAULT GETDATE()
);
GO

INSERT INTO dbo.goods (iGoodsID, sGoodsName, iGoodTotal, iPrice, iPriceTotal, iSortID)
VALUES
('YR6001', '瘦身羽絨服', 20, 200, 4000, 1001),
('YR6002', '加厚羽絨服', 20, 300, 6000, 1001),
('BB7001', '小黃牛皮馬鞍包', 30, 100, 3000, 1002),
('BB7002', '十字繡流蘇包', 50, 150, 7500, 1002);
GO

步驟 2:創建會產生死鎖的兩個事務

關鍵點:兩個事務以相反順序訪問相同資源,並使用 WAITFOR DELAY 增加並發窗口。

事務 1:先更新 goods_sort,再更新 goods

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRY
    BEGIN TRAN
        UPDATE dbo.goods_sort SET sSortName = '女鞋' WHERE iSortID = 1003;
        WAITFOR DELAY '00:00:05';  -- 故意延遲 5 秒
        UPDATE dbo.goods SET sGoodsName = '胖子羽絨服' WHERE iID = 2;
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF (XACT_STATE() = -1) ROLLBACK TRAN;
    IF (ERROR_NUMBER() = 1205) PRINT '事務 1 發生了死鎖';
    -- 此處可記錄錯誤日誌或返回應用程序
END CATCH

SELECT iID, sGoodsName FROM dbo.goods WHERE iID = 2;
SELECT iSortID, sSortName FROM dbo.goods_sort WHERE iSortID = 1003;
GO

事務 2:先更新 goods,再更新 goods_sort(順序相反)

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

BEGIN TRY
    BEGIN TRAN
        UPDATE dbo.goods SET sGoodsName = '瘦子羽絨服' WHERE iID = 2;
        WAITFOR DELAY '00:00:05';  -- 故意延遲 5 秒
        UPDATE dbo.goods_sort SET sSortName = '男鞋' WHERE iSortID = 1003;
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF (XACT_STATE() = -1) ROLLBACK TRAN;
    IF (ERROR_NUMBER() = 1205) PRINT '事務 2 發生了死鎖';
    -- 此處可記錄錯誤日誌或返回應用程序
END CATCH

SELECT iID, sGoodsName FROM dbo.goods WHERE iID = 2;
SELECT iSortID, sSortName FROM dbo.goods_sort WHERE iSortID = 1003;
GO

關鍵技術點解析

  1. TRY...CATCH 錯誤處理:確保死鎖發生時事務能正確回滾,並捕獲錯誤碼 1205
  2. WAITFOR DELAY:人為製造並發窗口,提高死鎖觸發概率
  3. ERROR_NUMBER() = 1205:精確識別死鎖錯誤(而非其他錯誤)
  4. XACT_STATE() = -1:檢測不可提交狀態,必須回滾

死鎖預防最佳實踐

  • 統一訪問順序:所有事務按相同順序訪問表/行(如按主鍵排序)
  • 縮短事務長度:減少鎖持有時間,避免在事務中進行網絡調用或用戶交互
  • 使用行版本控制:啟用 READ_COMMITTED_SNAPSHOT 減少讀寫衝突
  • 設置死鎖優先級:SET DEADLOCK_PRIORITY LOW/HIGH 控制犧牲者選擇
  • 監控與告警:使用擴展事件或 SQL Profiler 記錄死鎖圖,定期分析優化

死鎖日誌記錄範例

-- 在 CATCH 區塊中記錄死鎖詳情
INSERT INTO DeadlockLog (ErrorTime, ErrorNumber, ErrorMessage, TransactionName, HostName, AppName)
VALUES (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE(), 'UpdateGoods', HOST_NAME(), APP_NAME());

總結

理解死鎖產生條件、掌握檢測方法、實施預防策略是資料庫開發者的核心技能。通過統一訪問順序、縮短事務、啟用行版本控制等措施,可將死鎖風險降至最低。

copyright © 萬盛學電腦網 all rights reserved