在以下庫存系統中，是否可以強制行鎖以避免死鎖並確保其按預期工作？
我想為 M 個使用者自動保留 N 個對象的庫存。
我有兩張庫存跟踪表，一張用於全球庫存，一張用於個人庫存。
- 全域庫存表列：ObjectID uniqueidentifier、Count int
- 個人庫存表列：UserID nvarchar(64)、ObjectID uniqueidentifier、Count int
有兩個表，因為我必須為每個對像以及每個使用者強制執行最大允許保留。例如，一個對象可能被限制為總共保留 1000 個，每個使用者最多保留 10 個該對象。
全域庫存表是唯一鍵控的
$$ ObjectID $$，並且個人庫存表是唯一鍵控的$$ UserID, ObjectID $$.
- 主鍵是每個表上的唯一索引，因此只對行進行鎖定，而不會對其他索引鍵進行鎖定。
- 全域庫存表將始終駐留在單個數據庫中。
- 個人庫存表可以跨多個數據庫分片，因此更新全域和一個或多個個人表的事務可以分佈。
- 這個“保留庫存”事務是唯一將在這些表上執行的事務。
為 3 個對象預留庫存的範例請求如下所示。有些對像有限制，有些則沒有。
[ {ObjectID: 'A', QuantityToReserve: 1}, {ObjectID: 'C', QuantityToReserve: 2, GlobalMax: 1000, PersonalMax: 10}, {ObjectID: 'B', QuantityToReserve: 5} ]
- 這種為多個對象保留庫存的請求必須以原子方式完成，並且必須為所有對象成功。
- 在嘗試對個人表進行任何鎖定之前，始終會在全域表上取出一組完整的鎖。
原子預留是通過啟動一個事務，然後首先更新全域庫存表中的行來實現的，對於所有對象 ID，以升序排列。
沒有限制的對象的更新語句如下所示：
UPDATE [GlobalInventory] WITH (ROWLOCK, XLOCK, HOLDLOCK) SET [Count] = [Count] + @QuantityToReserve WHERE [ObjectID] = @ObjectID具有限制的對象的更新語句如下所示：
UPDATE [GlobalInventory] WITH (ROWLOCK, XLOCK, HOLDLOCK) SET [Count] = [Count] + @QuantityToReserve WHERE [ObjectID] = @ObjectID AND ([Count] + QuantityToReserve) < @GlobalMax類似的更新語句稍後用於更新和鎖定個人庫存表中的行，並添加了
$$ UserID $$在謂詞中（特別是與 UserIDs 表的連接）。 按順序更新行（按對象 id）有效地取消了按該順序記錄的排他行鎖，然後在事務的其餘部分保留這些行鎖（我相信這種情況會發生，即使沒有更新語句的 HOLDLOCK 提示）。其他並發事務將阻塞等待事務送出更新的行，然後其他事務才能獲得更新這些行所需的鎖。因為行鎖是按升序取出的，所以一旦獲取了集合中的所有鎖，就可以保證沒有其他事務持有任何這些獨占鎖。這是一個公認的事實。鎖定順序很重要（解鎖順序不重要）。問問 Linus：https ://yarchive.net/comp/linux/lock_ordering.html
**我的第一個問題是，這些更新語句會按預期工作嗎？**這個問題有多個部分，例如謂詞是否會辨識要更新的行以及在持有排他行鎖時（即在更新行之前）謂詞是否成立？我有正確的鎖定提示嗎？
由於我們已經確定沒有其他事務持有目前持有的任何鎖，因此從邏輯上講，沒有其他事務會嘗試更新或鎖定個人庫存表中具有相同對象 ID 的任何記錄。
在這一點上，我認為我需要強制數據庫引擎在更新個人庫存行時也使用行級鎖。原因是，如果它升級為頁面鎖定，它可能會無意中鎖定恰好在該頁面上但不屬於事務處理的對象 ID 集的記錄。
例如，假設一個並發事務鎖定了全域記錄“D”，因此它似乎與使用對象 A、B 和 C 的記錄的第一個事務完全無關。全域或個人庫存記錄都不應該重疊，所以應該有每個人都在使用的個人庫存記錄中沒有鎖定爭用。但是，如果這個並發事務在個人庫存表中取出頁級鎖，並且 D 的某些頁恰好包含對象 B 的記錄，事務 D 可能會無意中持有屬於 D 和 B 的記錄的頁鎖。同樣地，第一個事務也可能持有包含 B 和 D 的一些記錄的頁級鎖，並且兩個事務都不能繼續，因為每個事務都有另一個正在等待的鎖定頁。換句話說，
更新個人庫存表中的記錄有點複雜，因為它必須更新多行。更新語句仍將一次針對一個對象 ID 執行，但它將與建立一組使用者 ID 的臨時表連接。
UPDATE pi WITH (ROWLOCK, XLOCK, HOLDLOCK) SET [Count] = [Count] + @QuantityToReserve FROM PersonalInventory pi INNER JOIN @UserIDs uids on pi.UserID = uids.UserID WHERE [ObjectID] = @ObjectID AND ([Count] + QuantityToReserve) < @PersonalMax我的第二個問題是，這個連接到 UserIDs 表的更新語句是否會採用正確的排他行鎖，只有由於滿足謂詞而實際更新的記錄？
這種情況對這個系統的正確執行至關重要，所以如果不是，我想知道，我想知道為什麼。如果沒有持有預期的鎖，持有哪些鎖。請假設我在表上禁用了鎖升級。
其他注意事項
我擔心是否可以強制執行這種行級鎖，但後來我發現有一個表選項可以禁用鎖升級。我更關心的是正確性而不是性能。使用數據庫行鎖將比任何其他涉及到伺服器多次往返的鎖定解決方案快很多數量級。使用
sp_getapplock也不行，因為它會冗餘地執行全域庫存表上的鎖所實現的相同功能，同時不採取任何措施來防止我剛剛提到的頁面鎖蔓延。通過使用數據庫、行級鎖，多個並發事務可以同時快速完成，並且鎖爭用最少。這將導致原子的、高吞吐量的庫存預訂，而不必擔心在應用程序級別管理事務，這最終會更複雜且可靠性更低。如果有一種方法可以強制組合鍵的一部分駐留在不同的數據頁上，那麼頁鎖是可以接受的，但我認為這是不可能的。例如，如果我在 {ObjectID, UserID} 上鍵入個人表，我必須確保每個頁面最多包含一個對象 ID（和許多使用者）的記錄。
我的第一個問題是，這些更新語句會按預期工作嗎？
很有可能，但不確定。
SQL Server 保證它將遵守查詢的語義，以及由有效隔離級別確定的 ACID 合規級別。除此之外，所有的都是實現細節（包括使用什麼類型的鎖、何時以及持有多長時間）。
優化器很可能會為給定的語句和模式描述選擇一個單例搜尋瑣碎計劃。在執行期間，儲存引擎很可能會獲得與預期非常相似的鎖，但不能保證。
謂詞是否會辨識要更新的行
這是語句的語義，所以是的。
在持有排他行鎖時（即在更新行之前），謂詞是否成立？
獲取獨占鎖的確切時間取決於實現細節，並且會受到提示和計劃形狀的影響。SQL Server 確實保證一旦特定行被辨識為符合更新條件，它將保持不被其他並發事務修改，直到目前事務完成。兩個範例更新語句都標識了一行，因此在面對並發更新活動時不會出現關於集合成員資格和競爭條件的問題。
我有正確的鎖定提示嗎？
ROWLOCK：這個提示（不是“指令”），結合禁用表的鎖升級，應該在我能想到的所有情況下產生行級鎖定。請注意，鎖永遠不會升級到頁面級別。如果沒有這個提示，SQL Server 幾乎肯定會為給定的語句和架構選擇行鎖定。XLOCK：SQL Server 通常在標識要更新的行的訪問方法上使用更新鎖，在修改之前轉換為獨占（如有必要）。採用更新鎖（而不是共享鎖）可以防禦轉換死鎖的常見原因。更新鎖兼容共享鎖，但不兼容其他更新鎖。對於單操作員更新，引擎通常會立即獲取排他鎖。在這種XLOCK情況下，提示似乎沒有執行有用的功能，但可能是無害的。HOLDLOCK：這是SERIALIZABLE隔離級別表提示的同義詞。它本身並不影響鎖的持有時間。無論目前有效的隔離級別如何，都將使用可序列化的隔離語義訪問具有此提示的對象。這對物理鎖定的影響可能很複雜，尤其是與其他與隔離相關的提示（如XLOCK. 例如，更新可能會在沒有XLOCK提示的情況下使用 Range SU，但會使用 Range XX。通常，將半開範圍鎖定在感興趣的鍵下方可能比共享鎖定更能降低並發性。順便說一句：嘗試為不存在的庫存保留庫存ObjectID似乎是一個奇怪的操作，但可序列化的隔離無論如何都會使用 Range XX 鎖來保護周圍的範圍。在我看來，額外的提示似乎沒有什麼道理。使用可序列化事務執行簡單的更新似乎可以提供盡可能多的保證。在相同的訪問方法上以一致的順序訪問每一行應該提供足夠的保護，防止全域庫存表上的死鎖。
我的第二個問題是，這個連接到 UserIDs 表的更新語句是否會採用正確的排他行鎖，只有由於滿足謂詞而實際更新的記錄？
這不太確定，也很難完全評估。優化器對這條語句的執行計劃有真正的選擇。例如，它可以在散列、合併或嵌套循環連接、掃描或查找之間進行選擇，以及決定是否呼叫並行性、通過點陣圖過濾減少半連接、嵌套循環預取和批量排序……在許多可能性中。並非所有內部細節都反映在最終使用者可見的執行計劃表示中。
請特別注意，即使優化器選擇了問題文本中隱含的迭代執行計劃，它也可以選擇任一表作為驅動程序。也許“問題”最明顯的原因是優化器選擇完全掃描個人庫存表，並尋找表變數。
可以使用更多提示（例如
LOOP JOIN,FORCE ORDER等FORCESEEK）進一步約束優化器，但並非所有可能性都可以通過這種方式涵蓋。我會選擇另一種方法。