這種 SQL Server PK 違規怎麼可能發生？

我收到了一個我認為不可能的錯誤。我有一個如下所示的 SQL Server（版本 11.0.6594）表：

CREATE TABLE [IDMaster](
 [ID] [int] NOT NULL,
CONSTRAINT [PK_IDMaster] PRIMARY KEY CLUSTERED 
([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]

它基本上應該做 IDENTITY 現在所做的事情（已經有一段時間了）——它用於獲取新的唯一整數值，這些值在其他一些表中使用。

現在我有一個 SQL 語句，它選擇 MAX 值，添加到它，然後將該值插入到同一個表中——所有這些都在一個語句中。所以從理論上講，這句話不可能違反PK（儘管我認為可能會陷入僵局）。但是，不知何故，它得到了一個。

違反主鍵約束“PK_IDMaster”。無法在對象“IDMaster”中插入重複鍵。重複鍵值為 (25309587)。

我不知道這是怎麼可能的，但顯然它是；因為它剛剛發生。

想像一下嘗試從同一個表中生成新 ID 值的其他方法的任何瘋狂、奇怪的混合，它們可能與該系統中存在的方法相距不遠。但我想不出他們可能會導致該特定錯誤的任何方式。我的理解是，SQL Server 使用的事務鎖定強制在一個沒有 (NOLOCK) 或任何內容的 SQL 語句中，它所引用的行不會受到任何干擾。

唯一的其他因素，我認為可能不相關，但誰知道呢，是這個 SQL 包裝在一個 TRY 中，並設置了一些選項。這是出現該錯誤的 SQL：

SET XACT_ABORT ON;
SET IMPLICIT_TRANSACTIONS ON;
BEGIN TRY

INSERT INTO IDMaster (ID) 
SELECT COALESCE(
 -- make sure the new value is odd
 CASE WHEN MAX(COALESCE(ID,0)) % 2 = 0
  THEN MAX(COALESCE(ID,0)) + 1
  ELSE MAX(COALESCE(ID,0)) + 2
  END,1)
FROM IDMaster;

END TRY
BEGIN CATCH
 WHILE (@@TRANCOUNT > 0) ROLLBACK;
 THROW;
END CATCH
WHILE (@@TRANCOUNT > 0) COMMIT;

請解釋：

1. 怎麼可能得到那個錯誤？
2. 如果可能的話，我怎樣才能防止它發生（不改變表格）？

你的理論是錯誤的。從您的“身份”表中讀取不會鎖定它，插入它會鎖定它。雖然它們是單個複合語句的一部分，但它們仍然是單獨的操作。在讀取和插入之間的時間內，可以從表中讀取兩個（或更多）連接。插入使用鎖，因此實際上只有一個連接能夠將它們讀取的值寫入表中，其他連接將如您所發現的那樣獲得主鍵違規錯誤。

最好的解決方案是使用其中一種內置方法來處理此問題：標識列、序列或 guid。通常應該避免推出自己的解決方案：您根本沒有數據庫管理系統所具備的經驗或使用者基礎（並且您正在處理抽象，因此即使您是主要數據庫之一的程序員之一管理系統，您將無法做同樣的事情）。

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