IDENTITY_INSERT 如何影響並發？

我正在嘗試使用 3rd 方 SAP 載入項幫助客戶，該載入項出現發布故障並且已失去支持。

在某些情況下，它會從投遞隊列表歸檔和不完整投遞到投遞歸檔表。我需要將這些歸檔結果移回隊列中。

隊列 ID 是一個標識列，我想保持不變。

問題是，如果我執行 identity_insert on/insert/identity_insert off，對於創建隊列條目並期望自動生成標識列的程序的並發性，我可以期待什麼？

任何有關展示此類行為的最佳方式的指針也將不勝感激。

自行設置IDENTITY_INSERT ON不會消除並發性——這不會在表上放置任何排他鎖，只是一個簡短的模式穩定性 (Sch-S) 鎖。

因此，在預設行為下，理論上會發生什麼，您可以在會話 1 中執行此操作：

BEGIN TRANSACTION;

-- 1
SET IDENTITY_INSERT dbo.tablename ON;

-- 2
INSERT dbo.tablename(id, etc) VALUES(100, 'foo'); -- next identity is now 101

-- 3
INSERT dbo.tablename(id, etc) VALUES(101, 'foo'); -- next identity is now 102

-- 4
SET IDENTITY_INSERT dbo.tablename OFF;

COMMIT TRANSACTION;

在另一個會話中，您可以在表中的點 1、2、3 或 4 處插入行。這似乎是一件好事，除了發生在 2 和 3 之間的任何插入時，自動生成的值已觸發另一個會話基於語句 2 的結果 - 因此它將生成 101，然後語句 3 將因主鍵違規而失敗。WAITFOR使用一些s設置和測試自己非常簡單：

-- session 1
-- DROP TABLE dbo.what;
CREATE TABLE dbo.what(id INT IDENTITY PRIMARY KEY);
GO
BEGIN TRANSACTION;

SET IDENTITY_INSERT dbo.what ON;

INSERT dbo.what(id) VALUES(32);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(33);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(34);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(35);
WAITFOR DELAY '00:00:05';
INSERT dbo.what(id) VALUES(36);

SET IDENTITY_INSERT dbo.what OFF;

COMMIT TRANSACTION;

啟動該批次後，在另一個視窗中啟動該批次：

-- session 2
INSERT dbo.what DEFAULT VALUES;
WAITFOR DELAY '00:00:01';
GO 20

會話 2 應該只插入 1-20 的值，對吧？除了因為你的手動插入會話 1 已經更新了基礎身份，在某些時候會話 2 將在會話 1 停止的地方繼續，並插入 32、33 或 34 等。它將被允許這樣做，但是那麼會話 1 將在下一次插入時因 PK 違規而失敗（獲勝可能只是時間問題）。

解決此問題的一種方法是TABLOCK在第一次插入時呼叫 a ：

INSERT dbo.what WITH (TABLOCK) (id) VALUES(32);

這將阻止任何其他使用者嘗試使用該表插入（或真正做任何事情），直到您完成將這些歸檔行移回。當然，這會限制並發，但這是您希望阻止工作的方式。希望這不會以如此頻繁的速度發生，以至於您一直在阻止其他人。

其他幾個解決方法：

• 停止關心IDENTITY產生的價值。誰在乎？如果原始值非常重要，請使用 a UNIQUEIDENTIFIER（可能在單獨的表中生成，並以 a作為代理）。IDENTITY
• 將存檔過程更改為使用“軟刪除”，其中最初將某些內容標記為已存檔，並且在以後的某個日期之前存檔不會永久化。然後任何試圖將它們移回的程序都可以簡單地執行直接更新並修復軟刪除標誌。

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