Postgres 9.1 中的 ACID 違規？
我正在使用 Postgres DB 為大量電腦/程序實現作業調度。簡而言之，每個作業都有其 id，所有調度都通過三個選項卡實現：所有作業、目前正在執行的作業和已完成的作業。
調度的關鍵功能是 (1) 請求作業和 (2) 通知數據庫有關已完成的作業。請求作業從作業列表中獲取任何 ID，該 ID 不在執行表中，也不在已完成表中：
insert into piper.jobs_running select x.fid from ( SELECT fid FROM piper.jobs except select fid from piper.jobs_running except select fid from piper.jobs_completed ) as x limit 1 returning(fid)完成作業會將其從執行列表中刪除並將其插入到已完成列表中。因為它不是特定於並發的，所以我省略了 SQL 命令（完成一項工作需要幾十分鐘到幾個小時。）
對我來說，這是一個令人討厭的驚喜，兩個程序執行上面完全相同的查詢（幾乎同時請求作業）可能會獲得相同的作業 ID（fid）。我要提出的唯一可能的解釋是 Postgres 不依賴 ACID 符合。註釋？
**附加資訊：**我將事務設置為可序列化（在postgresql.conf
set default_transaction_isolation = 'serializable'中）。現在，如果隔離未完全填充，DBMS 會使事務失敗。是否可以強制 Postgres 自動重新啟動它們？
查詢的具體問題
PostgreSQL 預設為
READ COMMITTED隔離，看起來你沒有使用任何不同的東西。在READ COMMITTED每個語句中都有自己的快照。它看不到來自其他事務的未送出更改；就好像它們根本沒有發生一樣。現在，假設您在三個會話中同時執行此程序，在具有三個條目 in
jobs、零 injobs_running和零 in的設置上jobs_completed：insert into piper.jobs_running select x.fid from ( SELECT fid FROM piper.jobs except select fid from piper.jobs_running except select fid from piper.jobs_completed ) as x limit 1 returning(fid)每次執行都會選擇
jobs. 因為他們的快照都是在他們中的任何一個送出更改之前拍攝的，甚至是在創建未送出的行之前，*他們都會在jobs_runningand中找到零行jobs_completed。所以他們都要求一份工作。可能是同一個工作，因為即使沒有
ORDER BY，掃描順序也是一樣的。鎖定
行鎖定跨越國界，讓您在事務之間進行通信以強制排序。所以你可能認為這會解決你的問題，但它不會。
如果您
FOR UPDATE對row條目進行鎖定，因此該行被獨占鎖定，則鎖定將一直保持，直到事務送出或回滾。所以你會認為下一個事務會得到不同的行，或者如果它試圖得到相同的行，它會等待鎖釋放，看到現在有一個條目jobs_running，然後跳過該行。你錯了。將會發生的是您將鎖定所有行。一個事務將成功獲得所有行的鎖。將執行相同索引或順序掃描的其他事務通常會嘗試以相同的順序鎖定行，在第一次鎖定嘗試時卡住，並等待第一個事務回滾或送出。如果你不走運，它們可能會開始鎖定不同的行集並相互死鎖，從而導致死鎖中止，但通常你只是沒有獲得有用的並發性。
更糟糕的是，第一個事務選擇它鎖定的行，插入一行
jobs_running並送出，釋放鎖。然後另一個事務能夠繼續，並鎖定所有行……但它沒有獲得數據庫狀態的新快照（快照在語句開始時獲取），所以*它看不到你插入一行進jobs_running。因此，它抓取相同的作業，將該作業的一行插入到jobs_running中，然後送出。條件重新檢查
PostgreSQL 有一個大多數數據庫中不包含的古怪特性，如果一個事務在鎖上阻塞，它會在第一個事務送出後獲得鎖時重新檢查所選行是否仍然匹配鎖條件。
這就是https://stackoverflow.com/questions/11532550/atomic-update-select-in-postgres中的範例有效的原因 - 它依賴於
WHERE在回收鎖後重新檢查子句中的限定符。鎖定的使用會強制所有內容串列執行，因此在實踐中，您最好使用單個連接來完成這項工作。
隔離、酸和現實
PostgreSQL 中的事務隔離並不是事務並發執行的完美理想，但它們的效果與串列執行的效果相同。
唯一能提供完美隔離的真實世界數據庫是在寫入事務首次訪問每個表時獨占鎖定每個表的數據庫，因此在實踐中，事務只能是並發訪問，如果它是對數據庫的不同部分。沒有人願意在需要並發或有用的情況下使用這樣的數據庫。
所有現實世界的實現都是妥協。
READ COMMITTED預設PostgreSQL 的預設設置是
READ COMMITTED隔離，這是一個定義明確的隔離級別，允許不可重複和幻讀，如PostgreSQL 事務隔離手冊中所述。
SERIALIZABLE隔離您可以在每個事務的基礎上或作為每個使用者、每個數據庫或（不推薦的）全域預設值請求更嚴格的
SERIALIZABLE隔離。這提供了更強大的保證，儘管仍然不完美，但如果它們以串列執行時不可能發生的方式互動，則以強制回滾事務為代價。因為您的並發查詢將始終嘗試獲取第一個作業，無論有多少作業，除了一個之外的所有作業都會因序列化失敗而中止。因此，在實踐中，您不會獲得任何有用的並發性，還不如通過單一連接將工作分配給工作人員。
（請注意，在 PostgreSQL 9.1 之前，
SERIALIZABLE提供的保證要弱得多，並且不會檢測到很多事務相互依賴的情況。）自動重新執行
SERIALIZABLEPostgreSQL 不會自動重新執行
SERIALIZABLE由於序列化失敗而中止的事務。在某些情況下這會非常有用，但在其他情況下這樣做是完全錯誤和危險的——尤其是在涉及通過應用程序的讀取/修改/寫入周期的情況下。目前不支持在序列化失敗時自動重新執行事務。該應用程序預計會重試。不要自己動手寫排隊系統
看起來您正在嘗試做的是編寫一個排隊系統。考慮不這樣做。編寫一個健壯、可靠和正確的排隊系統是很困難的，並且已經有一些不錯的系統可供您採用。你必須處理諸如崩潰安全之類的事情，當有人接受一個任務然後未能完成它時會發生什麼，當你放棄任務處理程序完成它時，圍繞完成的競爭條件等等。有很多微妙的並發問題。不要嘗試DIY。
9.5 和
SKIP LOCKED仍在開發中的 PostgreSQL 9.5 添加了一項功能，使排隊變得相當容易。
它讓你說如果當你
SELECT ... FOR UPDATE，如果一行被鎖定，你應該忽略它並繼續尋找下一個未鎖定的行。這在與 a 結合使用時非常有用，LIMIT因為它可以說“找到其他人尚未嘗試聲明的第一行”。因此，編寫安全且並發地抓取作業的隊列變得非常簡單。在該功能可用之前，我強烈建議您堅持使用單連接隊列管理，或者使用經過良好測試的任務隊列系統。