死鎖 是指兩個或多個進程在執行過程中,因爭奪資源而造成的互相等待現象。在 SQL Server 中,當進程 A 持有資源 1 並請求資源 2,而進程 B 持有資源 2 並請求資源 1 時,雙方將陷入永久阻塞狀態。SQL Server 會自動檢測死鎖並選擇終止其中一個事務(犧牲者)以打破僵局。
SQL Server 內置死鎖監控器,定期掃描鎖管理器中的等待圖,尋找循環等待鏈。一旦發現死鎖,系統會根據死鎖優先級和執行成本選擇犧牲者,返回錯誤碼 1205 並回滾該事務。
-- 商品分類表
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
關鍵點:兩個事務以相反順序訪問相同資源,並使用 WAITFOR DELAY 增加並發窗口。
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
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
SET DEADLOCK_PRIORITY LOW/HIGH 控制犧牲者選擇-- 在 CATCH 區塊中記錄死鎖詳情
INSERT INTO DeadlockLog (ErrorTime, ErrorNumber, ErrorMessage, TransactionName, HostName, AppName)
VALUES (GETDATE(), ERROR_NUMBER(), ERROR_MESSAGE(), 'UpdateGoods', HOST_NAME(), APP_NAME());
理解死鎖產生條件、掌握檢測方法、實施預防策略是資料庫開發者的核心技能。通過統一訪問順序、縮短事務、啟用行版本控制等措施,可將死鎖風險降至最低。