INSERT 是否應該導致外鍵的排他鎖？

我正在處理一個死鎖問題。

程序 A 正在對具有 FK 到 TableB 的 TableA 進行簡單的 INSERT

程序 B 正在對包含 TableA 和 TableB 的連接執行複雜的 SELECT

我將在下麵包含跟踪資訊，但基本上我認為正在發生的事情是，由於 TableA 對 TableB 具有 FK，對 TableA 的插入導致 TableB 的 Primany INDEX 上出現排他鎖 (X)。我們確實為該 FK 啟用了參照完整性，但是通過插入 TableA 不需要對 TableB 進行更新，所以對我來說，需要排他鎖來檢查 FK 值是否存在似乎很奇怪。

這是預期的行為嗎？如果是這樣，我能做些什麼來減輕這種情況嗎？老實說，我沒想到這樣一個基本/香草插入會導致僵局。

此外，這不是我真正的問題，但如果你碰巧知道“subresources=FULL”是什麼意思，我很想知道。

編輯：只是為了清楚僵局：

processInserting 正在插入 TableA，並且在 TableB 的主索引（TableA 的外鍵）上具有 X 鎖。ProcessSelecting 正在等待此索引上的 RangeS-S 鎖定。

processSelecting 正在從包括 TableA 和 TableB 在內的許多表的連接中進行選擇，並且在 TableA 上有一個 S 鎖（因為它正在連接它）。ProcessInserting 正在等待此表上的 IX 鎖。

編輯2：提供更多細節。我呼叫 processSelecting 的“選擇”查詢是一個非常折磨人的查詢，它使用折磨人的視圖作為連接的一部分，所以看起來有點亂。

這是 RoutePlan (TableA) 和 Form (TableB) 表的 DDL。

http://pastebin.com/gWftciEG

RoutePlan 上有一個觸發器來記錄歷史更改：

http://pastebin.com/WjvNxPFK

執行 INSERT 到 Routeplan 的 SP 是：

http://pastebin.com/c6JqNFaX

執行 SELECT 的 SP 是：

http://pastebin.com/micNhuPf

並且 View CurrentAndPriorApprovers_View 被定義為

http://pastebin.com/hHSzNJyc

這是完整的跟踪日誌資訊

http://pastebin.com/qenwhJcN

resource-list

keylock hobtid=72057594059882496 dbid=66 objectname=TableB indexname=PK_TableB id=lock204d08500 mode=X associatedObjectId=72057594059882496

owner-list
owner id=processInserting mode=X

waiter-list
waiter id=processSelecting mode=RangeS-S requestType=wait

objectlock lockPartition=0 objid=516196889 subresource=FULL dbid=66 objectname=TableA id=lock2044c5080 mode=S associatedObjectId=516196889

owner-list
owner id=processSelecting mode=S
waiter-list
waiter id=processInserting mode=IX requestType=convert

這是我消除死鎖的第一個建議（這是一個非常常見的原因，每次都具有相同的解決方案）。您的RoutePlan_Save程序目前具有以下邏輯：

IF EXISTS (SELECT ... WHERE key = @key)
 UPDATE ... WHERE key = @key;
ELSE
 INSERT(key) VALUES(@key);

因此，無論您最終是要執行插入還是更新，這裡實際上有兩個查詢需要對錶進行鎖定。別那樣做！檢查行是否存在，然後在單獨的查詢中觸發更新語句，再次檢查行是否存在有什麼意義？這需要更多而不是更少的資源，而不是僅僅嘗試更新行而不知道它是否存在。所以改為這樣做：

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
 INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

您還可以通過使用不同的隔離級別來隔離其他只讀程序，該隔離級別不需要它們等待寫操作完成。NOLOCK / READ UNCOMMITTED是一個流行的，但是READ COMMITTED SNAPSHOT- 假設你可以在 tempdb 上受到打擊 - 恕我直言更好。還有其他選擇。我昨天碰巧寫了一篇關於這個的部落格：

• 壞習慣：把NOLOCK到處放

引用自：https://dba.stackexchange.com/questions/77655