文章出處
文章列表
某現場報一個SQL死鎖，于是開啟了1222跟蹤：
dbcc traceon(1222,-1)
一段時間之后拷貝ERROR文件查找相關信息，比較有用的摘錄出來如下：
語句一：
select study_iuid,station_aet,modality,accession_no,patient_fk,item_attrs,start_datetime
from worklist w WITH(readpast), mwl_item m
where w.TAG_STUDY_INSTANCE_UID=m.study_iuid
and isread='1' and (TAG_SPS_STATUS is null or TAG_SPS_STATUS='SCHEDULED' or TAG_SPS_STATUS='Discontinued')
and TAG_SPS_START_DATE between @P0 and @P1
and not exists (select 1 from mpps b where b.study_iuid=m.study_iuid)
語句二：
INSERT INTO mwl_item (created_time, updated_time, sps_id, start_datetime, station_aet, station_name, modality, perf_physician, perf_phys_fn_sx, perf_phys_gn_sx, perf_phys_i_name, perf_phys_p_name, req_proc_id, accession_no, study_iuid, item_attrs, sps_status, patient_fk)
VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17);
相關的死鎖資源如下：
resource-list
pagelock fileid=1 pageid=6996 dbid=8 objectname=Worklist.dbo.mwl_item id=lock19825c100 mode=IX associatedObjectId=72057594039697408
owner-list
owner id=process984d048 mode=IX
waiter-list
waiter id=process60e9708 mode=S requestType=wait
pagelock fileid=1 pageid=11086 dbid=8 objectname=Worklist.dbo.mwl_item id=lock1b087b100 mode=S associatedObjectId=72057594039697408
owner-list
owner id=process60e9708 mode=S
waiter-list
waiter id=process984d048 mode=IX requestType=wait
可以明顯的看到是select語句與insert語句產生了死鎖，爭用的資源分別6996和11086這兩個page，使用dbcc page查看這兩個頁:
dbcc traceon(3604)
go
dbcc page('Worklist',1,6996,3)
dbcc traceon(3604)
go
dbcc page('Worklist',1,11086,3)
發現11086是一個BLOB類型的數據頁，而6996是一個數據頁（或者說主鍵聚集索引的葉子頁）。查看表結構發現item_attrs是image（BLOB）類型。因此目前的鎖爭用情況如下：
1.select進程60e9708持有頁11086的S鎖，同時請求頁6996的S鎖。
2.insert進程984d048持有頁6996的IX鎖，同時請求頁11086的IX鎖。
下圖為抓到的相關deadlock graph，與上文中的進程ID不同，不過阻塞語句和情況都一樣。
其原理為：
1.由最初的303進程的insert語句產生數據頁4419的IX鎖，同時請求BLOB頁17741上的IX鎖以便插入BLOB數據，但此時數據頁17741上已有與IX不兼容的S鎖。
2.而select語句會在BLOB頁17741上加S鎖，返回請求數據頁4419的行數據失敗，因為4419頁被insert語句加了不兼容的IX鎖，因此兩個進程形成死鎖。
這個查詢語句是查詢當天的數據（這意味著查詢基本集中在最后幾頁），而mwl_item表的主鍵是自增長的ID，因此插入也是會插入到最后一頁。這樣造成查詢與插入頻繁在最后一頁生成死鎖。
可能的幾種優化方式：
1.優化select語句使其走mwl_item表的非聚集索引，但是由于本例是insert語句，insert會更新表上的所有索引，因此除非將BLOB列加入包含列，否則無法解決，但BLOB列是不能出現在索引中的。
2.通知開發優化相關代碼的執行頻度來避免死鎖。
3.升級程序版本，新版本沒有mwl_item表。
4.將with readpast換成with nolock，讀不加共享鎖。
文章列表
全站熱搜
留言列表
