死鎖對資料庫管理員（DBA）與開發人員而言並不陌生，其成因多樣。雖然開發人員在設計階段通常會考量並盡量避免死鎖，但在某些特殊應用場景——如高頻查詢、高併發存取——因資料庫設計的潛在缺陷，仍可能出現難以捕捉的死鎖，進而影響業務連續性。本文將深入剖析由設計問題引發的「鍵查找死鎖」，並提供實務解決方案。
為了模擬真實場景，我們建立測試環境並啟用 Trace Profiler 追蹤死鎖圖（Locks: Deadlock Graph），當然也可使用追蹤旗標或擴充事件來捕捉死鎖資訊。
create table testklup (
clskey int not null,
nlskey int not null,
cont1 int not null,
cont2 char(3000)
);
create unique clustered index inx_cls on testklup(clskey);
create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1);
insert into testklup select 1,1,100,'aaa';
insert into testklup select 2,2,200,'bbb';
insert into testklup select 3,3,300,'ccc';
declare @i int;
set @i = 100;
while 1 = 1
begin
update testklup set cont1 = @i where clskey = 1;
set @i = @i + 1;
end
declare @cont2 char(3000);
while 1 = 1
begin
select @cont2 = cont2 from testklup where nlskey = 1;
end
執行會話 2 片刻後，即可觀察到死鎖錯誤（圖 1-1），而在追蹤中捕捉到的死鎖圖如圖 1-2 所示。
圖 1-1 死鎖錯誤訊息
圖 1-2 死鎖圖分析
從死鎖圖可見：讀進程（SPID 108）請求寫進程（SPID 79）持有的排他鎖（X 鎖）而被阻塞，同時寫進程又申請讀進程持有的共享鎖（S 鎖），形成循環等待。關鍵在於讀操作的執行計畫（圖 1-3）涉及「鍵查找」，需回聚集索引取得 cont2 欄位，因此需要額外的 S 鎖；寫操作（圖 1-4）更新叢集索引鍵值，持有 X 鎖並需更新非叢集索引。
圖 1-3 讀操作執行計畫（含 Key Lookup）
圖 1-4 寫操作執行計畫
在預設隔離等級（Read Committed）下，讀取的 S 鎖屬瞬間獲取、讀完即釋放，不會等待交易結束。因此低併發、低頻率場景不易發生死鎖。但高頻模擬使 S 鎖獲取頻率極高，僅兩個會話（一讀一寫）即觸發死鎖。
核心原因： 讀查詢因非叢集索引未覆蓋 cont2，觸發鍵查找，產生額外的聚集索引鎖需求，與寫操作的鎖順序相反，導致死鎖。
針對「消除額外鍵查找鎖需求」與「讀操作避免獲取鎖」兩大方向，提供三種實務做法：
將查詢所需欄位 cont2 包含於非叢集索引中，使查詢無需回表：
CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ([cont2]);
先由非叢集索引取得叢集鍵，再由叢集索引取得目標欄位，雖為兩條語句，但各自鎖範圍明確，避免交叉：
declare @cont2 char(3000), @clskey int;
while 1 = 1
begin
select @clskey = clskey from testklup where nlskey = 1;
select @cont2 = cont2 from testklup where clskey = @clskey;
end
透過 WITH (NOLOCK) 或啟用 READ_COMMITTED_SNAPSHOT，採用樂觀並發，讀操作不申請 S 鎖，從根本上避免讀寫衝突：
declare @cont2 char(3000);
while 1 = 1
begin
select @cont2 = cont2 from testklup with (nolock) where nlskey = 1;
end
解決死鎖關鍵在於「看清本質」：找出鎖競爭的根本原因（本例為鍵查找產生的額外鎖），再依環境特性選擇最適合的方案。覆蓋索引效能最佳但增加儲存；分步查詢無需改動結構但需修改程式碼；NOLOCK 最簡單但可能讀到髒資料，需評估業務容忍度。實務上建議優先考慮覆蓋索引，兼顧效能與一致性。
