PostgreSQL 中並發 DELETE / INSERT 的鎖定問題
這很簡單，但我對 PG 所做的事情（v9.0）感到困惑。我們從一個簡單的表格開始：
CREATE TABLE test (id INT PRIMARY KEY);和幾行：
INSERT INTO TEST VALUES (1); INSERT INTO TEST VALUES (2);使用我最喜歡的 JDBC 查詢工具 (ExecuteQuery)，我將兩個會話視窗連接到該表所在的數據庫。它們都是事務性的（即 auto-commit=false）。我們稱它們為 S1 和 S2。
每個程式碼都相同：
1:DELETE FROM test WHERE id=1; 2:INSERT INTO test VALUES (1); 3:COMMIT;現在，以慢動作執行它，在視窗中一次執行一個。
S1-1 runs (1 row deleted) S2-1 runs (but is blocked since S1 has a write lock) S1-2 runs (1 row inserted) S1-3 runs, releasing the write lock S2-1 runs, now that it can get the lock. But reports 0 rows deleted. HUH??? S2-2 runs, reports a unique key constraint violation現在，這在 SQLServer 中執行良好。當 S2 執行刪除時，它報告刪除了 1 行。然後 S2 的插入工作正常。
我懷疑 PostgreSQL 鎖定了存在該行的表中的索引，而 SQLServer 鎖定了實際的鍵值。
我對嗎？這可以工作嗎？
Mat 和 Erwin 都是對的，我只是添加另一個答案，以進一步擴展他們所說的內容，而這不適合發表評論。由於他們的回答似乎不能讓大家滿意，並且有建議諮詢 PostgreSQL 開發人員，而我也是其中之一，我將詳細說明。
這裡重要的一點是，在 SQL 標準下，在
READ COMMITTED事務隔離級別執行的事務中，限制是必須不可見未送出事務的工作。當已送出事務的工作變得可見時，取決於實現。您要指出的是兩種產品選擇實現這一點的方式不同。兩種實現都沒有違反標準的要求。以下是 PostgreSQL 中發生的詳細情況：
S1-1 執行（刪除 1 行）舊行保留在原位，因為 S1 可能仍會回滾，但 S1 現在持有該行的鎖，以便任何其他嘗試修改該行的會話將等待查看 S1 是送出還是回滾。對錶的任何讀取仍然可以看到舊行，除非他們嘗試使用
SELECT FOR UPDATEor鎖定它SELECT FOR SHARE。S2-1 執行（但由於 S1 有寫鎖而被阻塞）S2 現在必須等待看到 S1 的結果。如果 S1 回滾而不是送出，S2 將刪除該行。請注意，如果 S1 在回滾之前插入了新版本，那麼從任何其他事務的角度來看，新版本將永遠不會存在，從任何其他事務的角度來看，舊版本也不會被刪除。
S1-2 執行（插入 1 行）這一行獨立於舊行。如果更新了 id = 1 的行，則新舊版本將相關，並且 S2 可以在該行解鎖時刪除該行的更新版本。新行恰好與過去存在的某些行具有相同的值，但這並不意味著它與該行的更新版本相同。
S1-3 執行，釋放寫鎖所以 S1 的變化是持久的。一排沒了。已添加一行。
S2-1 執行，現在它可以獲得鎖。但報告刪除了 0 行。啊？？？內部發生的情況是，如果更新了行，則存在從行的一個版本指向同一行的下一個版本的指針。如果該行被刪除，則沒有下一個版本。當一個
READ COMMITTED事務從一個寫入衝突的塊中喚醒時，它會跟隨更新鏈到最後；如果該行沒有被刪除並且仍然滿足查詢的選擇條件，它將被處理。該行已被刪除，因此 S2 的查詢繼續進行。S2 在掃描表期間可能會也可能不會到達新行。如果是這樣，它將看到新行是在 S2 的
DELETE語句開始之後創建的，因此它不是可見的行集的一部分。如果 PostgreSQL 使用新快照從頭開始重新啟動 S2 的整個 DELETE 語句，它的行為將與 SQL Server 相同。出於性能原因，PostgreSQL 社區沒有選擇這樣做。在這個簡單的情況下，您永遠不會注意到性能上的差異，但是如果您在被阻塞時將一千萬行放入 a
DELETE中，您肯定會注意到。這裡有 PostgreSQL 選擇性能的權衡，因為更快的版本仍然符合標準的要求。S2-2 執行，報告唯一鍵約束違規當然，該行已經存在。這是圖片中最不令人驚訝的部分。
雖然這裡有一些令人驚訝的行為，但一切都符合 SQL 標準，並且在該標準的“特定於實現”的範圍內。如果您假設某些其他實現的行為將出現在所有實現中，那肯定會令人驚訝，但是 PostgreSQL 非常努力地避免
READ COMMITTED隔離級別中的序列化失敗，並允許一些與其他產品不同的行為來實現這一點。現在，我個人不是任何產品實現中的
READ COMMITTED事務隔離級別的忠實擁護者。從交易的角度來看，它們都允許競爭條件產生令人驚訝的行為。一旦有人習慣了一種產品允許的奇怪行為，他們就會傾向於認為這種“正常”而另一種產品選擇的權衡取捨很奇怪。但是每個產品都必須為任何沒有實際實現為. PostgreSQL 開發人員選擇劃清界限的地方是盡量減少阻塞（讀取不會阻塞寫入，寫入不會阻塞讀取）並儘量減少序列化失敗的機會。SERIALIZABLE``READ COMMITTED該標準要求
SERIALIZABLE事務是預設的，但大多數產品並沒有這樣做，因為它會在更寬鬆的事務隔離級別上導致性能下降。有些產品在被選中時甚至不提供真正可序列化的事務SERIALIZABLE——尤其是 Oracle 和 9.1 之前的 PostgreSQL 版本。但是使用真正SERIALIZABLE的事務是避免競爭條件產生意外影響的唯一方法，並且SERIALIZABLE事務總是必須阻塞以避免競爭條件或回滾一些事務以避免發展中的競爭條件。事務的最常見實現SERIALIZABLE是嚴格的兩階段鎖定 (S2PL)，它同時具有阻塞和序列化故障（以死鎖的形式）。全面披露：我與麻省理工學院的 Dan Ports 合作，使用一種稱為 Serializable Snapshot Isolation 的新技術將真正可序列化的事務添加到 PostgreSQL 版本 9.1。
我相信這是設計使然，根據PostgreSQL 9.2的已送出讀隔離級別的描述：
UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜尋目標行方面的行為與 SELECT 相同：它們只會找到在命令開始時間1時送出的目標行。但是，這樣的目標行在找到時可能已經被另一個並發事務更新（或刪除或鎖定）。在這種情況下，可能的更新程序將等待第一個更新事務送出或回滾（如果它仍在進行中）。如果第一個更新程序回滾，則其效果被否定，第二個更新程序可以繼續更新最初找到的行。如果第一個更新程序送出，如果第一個更新程序刪除了該行，第二個更新程序將忽略該行2，否則它將嘗試將其操作應用於行的更新版本。
開始時您插入的行
S1還不存在。所以按照上面的（1）刪除它不會被看到。根據（2），刪除的被’s忽略。S2``DELETE``S2``S1``S2``DELETE所以在 中
S2，刪除什麼也不做。但是，當插入出現時，確實會看到S1插入：因為讀取已送出模式以一個新快照開始每個命令，該快照包括到該時刻送出的所有事務，所以同一事務中的後續命令在任何情況下都將看到已送出並發事務的影響。上面的問題是單個命令是否看到數據庫的絕對一致視圖。
因此，嘗試插入
S2失敗並違反約束。繼續閱讀該文件，使用可重複讀取甚至可序列化並不能完全解決您的問題 - 第二個會話將失敗並在刪除時出現序列化錯誤。
這將允許您重試事務。