簡(jiǎn)介圍繞 SQL Server 中一個(gè)看似反常的 Deadlock 案例這份資源面向數(shù)據(jù)庫(kù)開發(fā)、運(yùn)維與性能優(yōu)化人員系統(tǒng)梳理了死鎖的產(chǎn)生條件與完整分析路徑。文檔從一張同時(shí)帶有聚集索引和兩個(gè)非聚集索引的示例表出發(fā)先復(fù)現(xiàn) update 語(yǔ)句在 rowlock 下的互相阻塞再對(duì)比取消非聚集索引中的 include(d) 字段、將 d 列由 varchar(max) 改為 varchar(200) 兩種調(diào)整從而解釋索引鍵長(zhǎng)度、鎖粒度與死鎖觸發(fā)之間的關(guān)系。分析環(huán)節(jié)覆蓋 DBCC TRACEON(1222) 開關(guān)、SQL Profiler 按 SPID 篩選并捕獲 Locks/TSQL 事件、sp_readerrorlog 讀取死鎖報(bào)告以及通過(guò)執(zhí)行堆棧定位等待資源等典型方法能讓讀者形成一套可復(fù)用的死鎖排查思路。壓縮包內(nèi)為 1 個(gè) docx 文檔容量約 694KB內(nèi)容緊湊便于對(duì)照學(xué)習(xí)。該資源已有 326 人學(xué)習(xí)下載適合想深入理解 SQL Server 索引、鎖等待與死鎖機(jī)制的從業(yè)者參考。1. 這個(gè)死鎖怪在哪兒兩條一模一樣的 UPDATE鎖死了自己人先說(shuō)結(jié)論SQL Server 里兩條完全相同的 UPDATE 語(yǔ)句在并發(fā)執(zhí)行時(shí)發(fā)生了死鎖。更詭異的是把索引里的include(d)去掉或者把d從varchar(max)改成varchar(200)死鎖就消失了。這聽起來(lái)像是索引或者數(shù)據(jù)類型的“玄學(xué)”但背后是 SQL Server 執(zhí)行計(jì)劃對(duì)鎖申請(qǐng)順序的決定性影響。這個(gè)案例是我見過(guò)最典型的“死鎖不是等行鎖而是等執(zhí)行計(jì)劃里那幾步鎖”的教材。本文從復(fù)現(xiàn)步驟開始完整走一遍 1222 跟蹤、SQL Profiler 鎖事件分析、hobtid 定位索引、執(zhí)行計(jì)劃對(duì)比這幾板斧最終解釋清楚死鎖形成的完整鏈路。適合正在處理 SQL Server 死鎖、或者被“同樣的語(yǔ)句為什么這里鎖那里不鎖”困擾的 DBA 和開發(fā)人員。2. 復(fù)現(xiàn)這個(gè)死鎖從表結(jié)構(gòu)到并發(fā)觸發(fā)腳本2.1 三張索引的物理布局為什么它是死鎖的溫床原案例在 SQL 2008 上復(fù)現(xiàn)我這邊在 SQL Server 2019 兼容級(jí)別 100 的庫(kù)上也跑通了。核心在于這張表的結(jié)構(gòu)——它刻意制造了一個(gè)“更新一條記錄但索引鏈條很長(zhǎng)”的場(chǎng)景create table tt( id int identity primary key, a char(36), b char(36), d varchar(max) ) go create index ix_a_bc on tt(a) include(d) create index ix_b_cd on tt(b) include(d) go這里有兩個(gè)關(guān)鍵設(shè)計(jì)。第一id上的主鍵是 clustered index所以表本身按id物理排序更新d字段時(shí)基礎(chǔ)數(shù)據(jù)行的位置是確定的。第二ix_a_bc和ix_b_cd是 non-clustered index而且都在葉子節(jié)點(diǎn)里include(d)了。這就意味著當(dāng)UPDATE修改d時(shí)SQL Server 不僅要改 clustered index 里的數(shù)據(jù)行還得改這兩個(gè) non-clustered index 的葉子頁(yè)——因?yàn)樗鼈兊娜~子節(jié)點(diǎn)里存了這個(gè)字段的副本。varchar(max)的存在也很微妙。max 類型在大對(duì)象處理上有特殊的存儲(chǔ)和鎖行為它不會(huì)老老實(shí)實(shí)地把所有數(shù)據(jù)塞進(jìn)索引頁(yè)而是可能采用單獨(dú)的大對(duì)象分配單元。這一步給后面的問(wèn)題埋了個(gè)大雷。所以在動(dòng)手復(fù)現(xiàn)之前先確認(rèn)自己的環(huán)境里表和索引的實(shí)際物理布局而不是看邏輯結(jié)構(gòu)想當(dāng)然。2.2 插入一萬(wàn)行并定位一條“幸運(yùn)”記錄insert into tt select NEWID(),bbb,ddd go 10000這個(gè)go 10000在 SSMS 里表示執(zhí)行 10000 次插入用NEWID()生成a列的隨機(jī)值。a列是char(36)數(shù)據(jù)庫(kù)會(huì)用空格補(bǔ)齊尾部的差異但查詢時(shí)自動(dòng)截?cái)嗨圆挥绊懫ヅ洹２迦胪瓿珊笕〉?10 條記錄的a值作為后續(xù) UPDATE 的定位條件select * from tt where id 10這里有個(gè)值得說(shuō)明的點(diǎn)這一萬(wàn)條數(shù)據(jù)中a列的值全部通過(guò)NEWID()生成是唯一的而且因?yàn)殡S機(jī)分布ix_a_bc索引的鍵值排序會(huì)比較分散。這種離散性直接導(dǎo)致后續(xù) UPDATE 語(yǔ)句在索引上尋址時(shí)會(huì)經(jīng)歷 B-tree 的逐層定位而不是聚集掃描。我一般會(huì)順手跑一下DBCC SHOW_STATISTICS看看直方圖確保a列的分布能讓優(yōu)化器選擇 Index Seek否則整個(gè)復(fù)現(xiàn)路徑就變了。2.3 兩條死鎖循環(huán)rowlock 與“平均分配”的假象-- 連接 1 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3 -- 連接 2 while 1 1 update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3兩個(gè)連接各自開一個(gè)查詢窗口同時(shí)執(zhí)行這段循環(huán)死鎖在幾秒內(nèi)必然出現(xiàn)。with(rowlock)是刻意加上的它限定 SQL Server 只在行級(jí)加鎖避免鎖升級(jí)到頁(yè)鎖或表鎖后掩蓋掉真正的問(wèn)題。while 1 1讓 UPDATE 無(wú)限循環(huán)重復(fù)執(zhí)行同一行的更新。在實(shí)際復(fù)現(xiàn)時(shí)不要兩個(gè)窗口都手動(dòng)點(diǎn)執(zhí)行然后碰運(yùn)氣。更穩(wěn)妥的做法是先用一個(gè)連接把循環(huán)跑起來(lái)確認(rèn)單連接不會(huì)自鎖再啟動(dòng)第二個(gè)連接。批量測(cè)試時(shí)我會(huì)用兩個(gè)sqlcmd會(huì)話加上START時(shí)間對(duì)齊盡量模擬同一時(shí)刻的并發(fā)提交。這個(gè)場(chǎng)景下循環(huán)體沒(méi)有事務(wù)包裹每條 UPDATE 是隱式事務(wù)但死鎖檢測(cè)器依然能捕捉到鎖等待環(huán)。2.4 對(duì)比實(shí)驗(yàn)改動(dòng)索引或類型死鎖為什么消失原案例里另外兩個(gè)測(cè)試構(gòu)成了對(duì)照。測(cè)試二把兩個(gè) non-clustered index 里的include(d)去掉測(cè)試三把d改為varchar(200)。兩者都不再死鎖。在執(zhí)行計(jì)劃層面這兩次改動(dòng)的共同點(diǎn)是UPDATE 不再需要對(duì)三個(gè)索引分別做 X 鎖申請(qǐng)SQL Server 只更新 clustered index 主數(shù)據(jù)行。提示include(d)不是“附帶一個(gè)字段”這么簡(jiǎn)單它讓索引葉子節(jié)點(diǎn)物理包含這個(gè)字段的副本。UPDATE 修改該字段時(shí)所有包含該字段的索引對(duì)應(yīng)的葉子頁(yè)都要維護(hù)。維護(hù)的索引越多鎖的生命周期越長(zhǎng)死鎖概率越高。把varchar(max)換成varchar(200)不再死鎖的原因稍微不同后續(xù)章節(jié)會(huì)詳細(xì)展開執(zhí)行計(jì)劃的差異。復(fù)現(xiàn)階段把這個(gè)對(duì)照組跑一遍能很快讓新手體會(huì)到“死鎖不是靠猜是靠執(zhí)行計(jì)劃鎖序”。3. 收集死鎖證據(jù)打開 1222 開關(guān)與 SQL Trace 抓鎖事件3.1 開啟 1222 跟蹤死鎖報(bào)告的“黑匣子”SQL Server 默認(rèn)把死鎖信息寫到錯(cuò)誤日志但只記錄受害者會(huì)話的部分信息。開啟 1222 跟蹤后日志里會(huì)輸出完整的死鎖圖包括兩個(gè)參與進(jìn)程的鎖等待鏈、資源模式、SQL 文本和事務(wù)時(shí)間線。操作如下dbcc traceon (1222, -1)-1表示全局開啟對(duì)所有會(huì)話生效而不是只對(duì)當(dāng)前會(huì)話。開啟后死鎖一旦發(fā)生錯(cuò)誤日志會(huì)記錄一段以deadlock-list開頭的文本。關(guān)閉跟蹤用dbcc traceoff (1222, -1)。注意生產(chǎn)環(huán)境開啟 1222 會(huì)有輕微性能開銷建議只在復(fù)現(xiàn)窗口期內(nèi)開啟用完即關(guān)。sp_readerrorlog是讀錯(cuò)誤日志最快的入口sp_readerrorlog 0, 1, deadlock-list第三個(gè)參數(shù)可以過(guò)濾關(guān)鍵字。實(shí)際輸出里的死鎖報(bào)告關(guān)鍵部分長(zhǎng)這樣子deadlock victimprocess5e27708 process idprocess5e27708 ... lockModeX ... spid60 process idprocess5e09dc8 ... lockModeU ... spid54 resource-list keylock hobtid72057594066108416 ... indexnameix_a_bc ... modeU owner idprocess5e09dc8 modeU waiter idprocess5e27708 modeX keylock hobtid72057594065518592 ... indexnamePK__tt__3213E83F10E07F16 ... modeX owner idprocess5e27708 modeX waiter idprocess5e09dc8 modeU這段內(nèi)容能直接看到鎖等待方向spid 60 持有主鍵上的 X 鎖等待ix_a_bc上的 X 鎖spid 54 持有ix_a_bc上的 U 鎖等待主鍵上的 U 鎖。這就是一個(gè)典型的環(huán)狀等待。但 1222 不會(huì)告訴你鎖的申請(qǐng)順序——為什么兩個(gè)進(jìn)程各自持有第一個(gè)鎖之后會(huì)去申請(qǐng)對(duì)方的鎖3.2 通過(guò) SPID 過(guò)濾 SQL Profiler 的 Locks 事件要還原鎖的申請(qǐng)順序SQL Trace 是更好的工具。SQL Profiler 新建一個(gè) Trace在事件選擇里勾上 Show all events 和 Show all columns然后從 Locks 類別選Lock:Acquired、Lock:Released、Lock:Deadlock等事件從 TSQL 類別選SQL:BatchStarting和SQL:BatchCompleted。Column Filters 里按 SPID 過(guò)濾只保留死鎖涉及的兩個(gè)連接外加后臺(tái)進(jìn)程的 SPID。select spid在死鎖發(fā)生前分別到兩個(gè)連接里執(zhí)行這條語(yǔ)句拿到各自 SPID。原案例里一個(gè)是 54一個(gè)是 60。過(guò)濾時(shí)只選這兩個(gè) SPID 加系統(tǒng)進(jìn)程的 ID。Trace 輸出里L(fēng)ock:Acquired 和 Lock:Released 的 Mode、ObjectID、ObjectID2 是判斷鎖類型的入口。ObjectID 是索引對(duì)象 IDObjectID2 是 hobtid也就是 1222 報(bào)告里的那個(gè) 15 位數(shù)字。3.3 hobtid 到索引名的精確映射1222 輸出的keylock hobtid72057594066108416一串?dāng)?shù)字難以直接看出是哪個(gè)索引。常規(guī)做法是查sys.indexes、sys.objects和sys.partitions三張系統(tǒng)視圖的關(guān)聯(lián)select o.name as table_name, i.name as index_name, i.type_desc, p.partition_id from sys.indexes i inner join sys.objects o on i.object_id o.object_id inner join sys.partitions p on p.index_id i.index_id and p.object_id i.object_id where p.partition_id in ( 72057594065518592, 72057594066108416, 72057594066173952 )這條查詢把跟蹤日志里的hobtid轉(zhuǎn)成表名和索引名。partition_id就是 hobtid每個(gè)索引的每個(gè)分區(qū)有獨(dú)立值。注意 SQL Server 2008 以后一個(gè)索引對(duì)應(yīng)一個(gè)分區(qū)時(shí)hobtid和partition_id一一對(duì)應(yīng)如果表做了分區(qū)同一個(gè)索引會(huì)有多個(gè)hobtid排查時(shí)需要結(jié)合partition_number去辨別實(shí)際命中了哪個(gè)分區(qū)。提示把這條查詢里的partition_id換成object_id也適用于sys.dm_db_index_operational_stats這類 DMV 的排查場(chǎng)景能直接看到每個(gè)索引上的等待統(tǒng)計(jì)。3.4 鎖申請(qǐng)時(shí)間線誰(shuí)先 U 鎖誰(shuí)后 X 鎖從 Profiler 拉出來(lái)的 Lock:Acquired 和 Lock:Released 記錄按時(shí)間排序后一次成功的 UPDATE 的鎖生命周期清晰可見表 3-1一次成功 UPDATE 的鎖申請(qǐng)順序時(shí)間序索引鎖類型會(huì)話動(dòng)作1ix_a_bcUAcquired2PKclusteredUAcquired3PKclusteredXAcquired4ix_a_bcUReleased5ix_a_bcXAcquired6ix_b_cdXAcquired7ix_b_cdXReleased8ix_a_bcXReleased9PKclusteredXReleased從這張時(shí)間線能看到兩個(gè)關(guān)鍵階段。第一階段UPDATE 語(yǔ)句通過(guò)ix_a_bc的 Index Seek 定位到符合條件的記錄期間對(duì)ix_a_bc加 U 鎖確認(rèn)記錄存在后再對(duì) clustered index 的主鍵行加 U 鎖然后升級(jí)成 X 鎖執(zhí)行更新。第二階段SQL Server 發(fā)現(xiàn)d字段被兩個(gè) non-clustered index 的葉子頁(yè)包含于是回過(guò)頭來(lái)為兩個(gè)索引補(bǔ) X 鎖維護(hù)索引數(shù)據(jù)。這個(gè)“先更新主表再回來(lái)更新索引”的二次尋路過(guò)程就是死鎖的溫床。4. 死鎖形成鏈路執(zhí)行計(jì)劃里的 Index Update 與兩步走的陷阱4.1 鎖環(huán)怎么套上的連接 A 等主鍵、連接 B 等輔助索引結(jié)合 1222 報(bào)告和上表死鎖的直接因果關(guān)系能描述得很具體連接 Aspid 54完成第一階段在ix_a_bc上持 U 鎖做了 Index Seek然后準(zhǔn)備對(duì) clustered index 上的目標(biāo)行申請(qǐng) U 鎖時(shí)發(fā)現(xiàn)該行已被連接 B 加了 X 鎖。連接 Bspid 60恰好走完第一階段正在第二階段對(duì)ix_a_bc補(bǔ) X 鎖但ix_a_bc上有連接 A 的 U 鎖。于是連接 A 等主鍵連接 B 等輔助索引形成完整環(huán)形等待SQL Server 死鎖檢測(cè)器挑一個(gè)犧牲品回滾。這里有個(gè)耐人尋味的細(xì)節(jié)連接 B 在第一階段結(jié)束時(shí)釋放了ix_a_bc的 U 鎖連接 A 才申請(qǐng)到 U 鎖然后連接 B 在第二階段又回來(lái)?yè)?X 鎖。也就是說(shuō)同一個(gè)索引、同一個(gè)鍵的鎖在同一個(gè)事務(wù)里被釋放后又被不同模式重新申請(qǐng)。這個(gè)釋放后重申請(qǐng)的窗口恰恰是另一個(gè)連接插入的時(shí)機(jī)。4.2 鎖申請(qǐng)與釋放的“兩步走”Index Update 與 Lazy Index Maintenanceset statistics profile on go update tt with(rowlock) set d cd where a EF211985-EA72-4A40-81DA-0AAB076E7AA3set statistics profile on輸出文字形式的執(zhí)行計(jì)劃每行代表一個(gè)物理操作。測(cè)試一中能看到三個(gè)Index Update它們的父節(jié)點(diǎn)是同一個(gè)Update說(shuō)明 SQL Server 將主鍵更新、ix_a_bc更新、ix_b_cd更新拆成了三個(gè)獨(dú)立的物理操作。測(cè)試二中輔助索引不包含d字段所以只有一個(gè)Index Update鎖申請(qǐng)鏈路縮短死鎖消失。測(cè)試三中d為varchar(200)時(shí)執(zhí)行計(jì)劃里只有一個(gè) Update 節(jié)點(diǎn)但它的 child 節(jié)點(diǎn)同時(shí)包含三個(gè)對(duì)象的操作說(shuō)明引擎在一步內(nèi)完成了三處索引維護(hù)。測(cè)試一這種“先更新主表、再逐個(gè)更新二級(jí)索引”的行為在內(nèi)部叫作 Lazy Index Maintenance它把索引維護(hù)推遲到了主表更新之后帶來(lái)的代價(jià)就是多階段鎖申請(qǐng)。varchar(200)之所以能避免死鎖本質(zhì)是優(yōu)化器計(jì)算出大對(duì)象不再需要單獨(dú)的行外存儲(chǔ)索引葉子頁(yè)可以容納該字段于是選擇了 eager index maintenance——在一個(gè)算子內(nèi)完成全部更新鎖生命周期被壓縮到極小。4.3 為什么 varchar(max) 放進(jìn) include 會(huì)催化這個(gè)鎖環(huán)varchar(max)與普通varchar在存儲(chǔ)上有本質(zhì)區(qū)別max 類型默認(rèn)使用 large object 分配單元數(shù)據(jù)可能存儲(chǔ)在行外。當(dāng)它被include進(jìn)非聚集索引后索引維護(hù)算子需要處理行外數(shù)據(jù)的定位和搬遷這一步比普通定長(zhǎng)字符串的原地更新昂貴得多。優(yōu)化器在代價(jià)估算中發(fā)現(xiàn)行外存儲(chǔ)的更新不值得交付給單個(gè)算子做于是把輔助索引的更新拆成獨(dú)立算子形成了那三個(gè) Index Update。注意不要只關(guān)注死鎖本身。把varchar(max)放進(jìn)索引的葉子節(jié)點(diǎn)本身就意味著每一次UPDATE都要維護(hù) LOB 指針寫入放大效應(yīng)明顯。死鎖只是暴露了這個(gè)問(wèn)題真正的改進(jìn)方向是索引設(shè)計(jì)不合理。4.4 執(zhí)行計(jì)劃對(duì)比三個(gè)方案的鎖數(shù)量與死鎖概率把三次測(cè)試的執(zhí)行計(jì)劃并排看差異非常直觀表 4-1三次測(cè)試的執(zhí)行計(jì)劃與鎖行為對(duì)比場(chǎng)景d 列類型索引是否 include(d)執(zhí)行計(jì)劃中的 Update 節(jié)點(diǎn)死鎖是否發(fā)生測(cè)試一varchar(max)是1 個(gè) Update 3 個(gè) Index Update是測(cè)試二varchar(max)否1 個(gè) Update 1 個(gè) Index Update否測(cè)試三varchar(200)是1 個(gè) Update內(nèi)部合并更新三個(gè)索引否測(cè)試二的鎖少是因?yàn)檩o助索引里沒(méi)有dSQL Server 根本不需要碰它們。測(cè)試三的鎖數(shù)量和測(cè)試一相同但申請(qǐng)順序從“兩步走”變成了“一步到位”鎖等待窗口消失。這個(gè)對(duì)比印證了一件事死鎖的關(guān)鍵變量不是鎖的數(shù)量而是鎖的申請(qǐng)持續(xù)時(shí)間和順序。5. 避坑指南分析死鎖時(shí)最容易翻車的五個(gè)細(xì)節(jié)5.1 只看 1222 報(bào)告就下結(jié)論漏掉鎖申請(qǐng)順序是最大的坑現(xiàn)象1222 報(bào)告清楚顯示了兩個(gè)進(jìn)程各自持有什么鎖、等待什么鎖但看完了還是不知道為什么持有這些鎖也沒(méi)法解釋死鎖成因。原因1222 是死鎖發(fā)生瞬間的靜態(tài)快照它不包含鎖的申請(qǐng)時(shí)間線。解決必須配合 SQL Profiler 的 Lock:Acquired 和 Lock:Released 事件按時(shí)間排序還原鎖生命周期才能真正理解死鎖形成鏈路。我從這案例里學(xué)到的習(xí)慣是所有死鎖分析都至少保留一份鎖事件跟蹤否則只能停留在“看到環(huán)”的層面。5.2 用 spid 過(guò)濾 Trace 時(shí)漏掉系統(tǒng)進(jìn)程現(xiàn)象Trace 里只看到兩個(gè)死鎖會(huì)話的鎖事件但關(guān)鍵的中間環(huán)節(jié)缺失鎖的時(shí)序斷檔。原因SQL Server 的鎖管理和死鎖檢測(cè)還涉及多個(gè)后臺(tái)系統(tǒng)會(huì)話比如 LOCK_MONITOR 等這些進(jìn)程的鎖事件也應(yīng)該被捕獲。解決在原案例中過(guò)濾條件里除了 54 和 60還應(yīng)該加上 6 和 20 這類關(guān)鍵系統(tǒng) SPID。一般做法是先啟用 Locks 事件并不過(guò)濾發(fā)生死鎖后用小范圍的重放來(lái)定位避免第一次 Trace 就漏數(shù)據(jù)。5.3 用 ObjectID 而不是 ObjectID2 關(guān)聯(lián)索引現(xiàn)象Trace 里 ObjectID 是索引的 object_id去看 SysIndexes 時(shí)匹配到的是表而不是具體索引定位錯(cuò)誤。原因SQL Server 在鎖事件里的 ObjectID 指的是對(duì)象 ID而 ObjectID2 才是 hobtid也就是 1222 報(bào)告里 keylock 后面那串?dāng)?shù)字。解決直接用 ObjectID2 去關(guān)聯(lián) sys.partitions.partition_id。這是我做死鎖排查時(shí)用的固定腳本每次 Trace 之后立即執(zhí)行避免靠記憶硬編碼。5.4 把 rowlock 提示當(dāng)成解決死鎖的銀彈現(xiàn)象給 UPDATE 加上 with(rowlock) 后死鎖依然發(fā)生。原因rowlock 只是告訴優(yōu)化器行級(jí)鎖是允許的它不改變執(zhí)行計(jì)劃的形狀也不改變鎖申請(qǐng)順序。解決用它復(fù)現(xiàn)問(wèn)題時(shí)是為了防止鎖升級(jí)掩蓋掉真實(shí)沖突解決死鎖還是得靠執(zhí)行計(jì)劃調(diào)優(yōu)、索引調(diào)整或事務(wù)邊界改造。不要在產(chǎn)品代碼里依賴鎖提示來(lái)“修死鎖”后續(xù)維護(hù)是噩夢(mèng)。5.5 測(cè)試“去掉 include(d)” 后順手刪了索引破壞了原有查詢性能現(xiàn)象去掉 include(d) 后死鎖消失但原有查詢變慢甚至從 Index Seek 變成 RID Lookup。原因include 列本來(lái)是為了覆蓋常用查詢移除后 leaf 頁(yè)不包含 d 字段查詢定位到索引條目后還要回表。解決這個(gè)場(chǎng)景下更穩(wěn)妥的改法是把varchar(max)改成varchar(8000)或者直接改成varchar(200)保留 include 的結(jié)構(gòu)同時(shí)消除大對(duì)象的行外存儲(chǔ)問(wèn)題。要記住死鎖是索引設(shè)計(jì)不合理的最外層癥狀不要只治癥狀。6. 從執(zhí)行計(jì)劃預(yù)判死鎖用 Include/Exclude 索引反向檢查鎖序這個(gè)案例給了我一個(gè)很實(shí)用的方法論在代碼評(píng)審階段用set statistics profile on或set statistics xml on看 UPDATE 語(yǔ)句的輸出數(shù)一下Index Update算子數(shù)量。一個(gè) UPDATE 如果對(duì)應(yīng)多個(gè)Index Update就意味著多個(gè)物理操作分散在事務(wù)時(shí)間軸上鎖的持有時(shí)間會(huì)被拉長(zhǎng)死鎖風(fēng)險(xiǎn)隨之上升。這個(gè)方法在表結(jié)構(gòu)變更前就能發(fā)現(xiàn)問(wèn)題不需要等死鎖真的發(fā)生。具體的操作流程是先拿到目標(biāo) UPDATE 語(yǔ)句在測(cè)試庫(kù)上執(zhí)行set statistics profile on把輸出保存下來(lái)。然后手工統(tǒng)計(jì) Update 節(jié)點(diǎn)下的 Index Update 數(shù)量以及涉及的索引名。如果數(shù)量大于等于 2就要警惕。下一步結(jié)合索引定義檢查看這些索引的葉子節(jié)點(diǎn)是否包含了 UPDATE 會(huì)修改的字段。如果包含就得評(píng)估這些索引是否可以去掉或者把大字段從 include 里移除。這里可以套用我在這個(gè)案例里沉淀下的檢查清單數(shù) Index Update 算子數(shù)量超過(guò) 2 個(gè)標(biāo)黃。檢查涉及更新的索引里有沒(méi)有 include 正在被修改的列。檢查被修改列的數(shù)據(jù)類型varchar(max)、nvarchar(max)優(yōu)先標(biāo)紅。模擬兩個(gè)并發(fā)會(huì)話用循環(huán) UPDATE 驗(yàn)證是否存在鎖等待不要只測(cè)單會(huì)話執(zhí)行。把varchar(max)從索引里移除或者改成nvarchar(4000)這類帶長(zhǎng)度限制的類型通常是最直接的優(yōu)化。如果業(yè)務(wù)需要長(zhǎng)文本字段的覆蓋索引考慮改用nvarchar(4000)加上全文索引或者把長(zhǎng)文本拆表存放。那以后我每次做表結(jié)構(gòu)評(píng)審都會(huì)把“這張表有哪些索引包含了大對(duì)象列”當(dāng)成必查項(xiàng)目。這個(gè)案例也讓我養(yǎng)成了一個(gè)習(xí)慣所有新增索引的腳本都先跑一次set statistics profile on驗(yàn)證 UPDATE 和 DELETE 的執(zhí)行計(jì)劃形狀確認(rèn)沒(méi)有多算子索引維護(hù)再進(jìn)入發(fā)布流程。對(duì)于死鎖這種問(wèn)題事后排查的能力很重要但從執(zhí)行計(jì)劃里提前看見風(fēng)險(xiǎn)才是更省時(shí)間的做法。希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取