• 原文地址：Practical Guide to SQL Transaction Isolation
• 原文作者：Joe Nelson
• 譯文出自：掘金翻譯計劃
• 本文永久連結：github.com/xitu/gold-m…
• 譯者：sigoden
• 校對者：mnikn, tmpbook

SQL 事務隔離實用指南

你可能已經在你的資料庫文件中看到過隔離級別這一個概念，雖然感到有點不安，但是並沒有太放在心上。一些日常的例子中使用到的事務本質上是隔離。大多數人使用資料庫的的預設隔離級別，並期望得到最好結果。隔離級別是一個必須要理解的基本概念，而且如果你花點時間學習這個指南，你會覺得生活更愜意。

我從學術論文中，從 PostgreSQL 文件中，在與同事就什麼是隔離級別，什麼時候使用它們能在保持應用程式的正確性的同時獲得最大執行效率等問題答案的討論中收集了本文需要的資訊。

基本定義

為了正確理解 SQL 隔離級別，我們需要先思考事務本身。事務的概念來自於如下契約規則：合法交易必須具有原子性（所有條款都同時適用或同時失效），一致性（遵守法律協議），永續性（承諾後各方不能收回承諾）。這些性質就是資料庫管理系統中眾所周知的縮寫詞 ACID 中的 A，C 和 D。最後一個字母 I，意思是隔離，就是本文要重點討論的了。

在資料庫中而非法律意義中，事務是一組操作，將資料庫從一個一致性狀態轉變到另一個一致狀態。這意味著，如果所有的資料庫一致性約束條件在執行事務前是滿足的，那麼在執行後仍然是滿足的。

資料庫能將這一思想更進一步，在每一條 SQL 資料變更語句中都強加約束嗎？現有的 SQL 命令做不到。它們表達力不足以保證使用者的每一步執行都保持一致性。舉一個經典的例子，將一個銀行帳戶的錢轉移到另一個賬戶這個過程中，在我們將錢從一個賬戶扣除之後，並把錢計入另一個賬戶之前，存在著一個暫時的不一致狀態。因為這個原因，事務而不是語句被作為一致性的基本單位。

在這一觀念之上，我們可以想象事務在資料庫上連續執行著，並一直等待直到輪到它來獨自處理資料的時候。在這個有序的世界中，資料庫將從一個一致的狀態移動到另一個一致的狀態，中途會短暫地出現的不一致狀態，但並不會造成有害的影響。

然而，序列事務這麼烏托邦的事情在任何多使用者資料庫系統都幾乎是不可行的。想象一下，一家航空公司的資料庫因為一個使用者預定航班而被鎖定，導致任何人都無法訪問。

值得慶幸的是完全序列執行事務通常是不必要的。許多事務不會對其它事務產生干擾，因為它們更新或讀取的資訊被完全隔離。同時執行此類事務（交錯執行其命令）的最終結果與選擇在另一個事務之前才執行這個事務沒有什麼區別。這種情況下的事務，我們稱之為可序列化。

然而，並行執行事務確實有造成衝突的風險。沒有資料庫監督，事務會干擾彼此的工作資料，並執行在不正確的資料庫狀態中。這可能會導致查詢結果不正確和違反約束。

現代資料庫提供了一些方式自動地選擇性地在一個事務中通過低延時或重試命令來避免干擾。資料庫為了預防事務間的干涉提供了幾種嚴格程度遞增的模式，被稱作隔離級別。更高等級的隔離級別在檢測和處理衝突上更有效，但也更耗費資源。

併發事務提供了不同等級的隔離級別給開發者，開發者能夠平衡併發量和吞吐量，由此來確定隔離等級。較低的隔離級別會提高事務併發量，但也增加了事務執行在某種不正確資料庫狀態中的風險。

我們首先要理解哪些併發互動會對應用所需的查詢操作造成威脅，然後才能選擇合適的隔離等級。正如我們將看到的，有時一個應用程式可以通過手動操作（如採取顯示鎖定）來降低它常規情況下需要的隔離級別。

在研究隔離級別之前，讓我們先停下來看看“動物園”中圈養的事務問題。文獻稱這類問題為“事務現象”。

事務現象“動物園”

對於每一種現象，我們將深入探究它併發命令示意圖，分析它為什麼有問題，它在何種情況下可以被接受，以及它在什麼情況下是我們為達到特定效果有意使用的。

我們將用一種速記符號表示事務 T1 和 T2 的執行。下面是一些例子：

• r1[x] —— T1 讀取行 x 的值
• w2[y] —— T2 寫入行 y 的值
• c1 —— T1 提交
• a2 —— T2 中斷

髒寫

事務 T1 修改條目，事務 T2 在事務 T1 提交或回滾前進一步修改。

模型

危害

如果我們允許髒寫，那麼我們將不能確保一定可以回滾事務，想一下這種情況：

• { 資料庫在狀態 A }
• w1[x]
• { 資料庫在狀態 B }
• w2[x]
• { 資料庫在狀態 C }
• a1

我們應該回退到狀態 A 嗎？不，因為那樣我們會失去 w2[x] 。所以我們應該保持在狀態 C。如果 c2 發生那麼一切就正常了。然而如果 a2 發生了會怎樣？我們不能回退到狀態 B，因為那樣會丟棄 a1。但我們不能回退到狀態 C，因為那樣會丟棄 a2。歸謬法可以論證。

因為髒寫打破了事務的原子性，即使是在最低隔離級別，沒有任何的關聯式資料庫允許這些操作。通過抽象的方式考慮這個問題，是很具有啟發性的。

髒寫還會破壞一致性。例如，假設約束是 x=y。事務 T1 和 T2 單獨執行都能保證約束，但是它們一起執行將違法約束。

• start, x = y = 0
• w1[x=1] … w2[x=2] … w2[y=2] … w1[y=1]
• now x = 2 ≠ 1 = y

合理用法

在任何情況下，髒寫的都是沒有意義的，也不能提供便捷。因此，沒有資料庫允許它們。

髒讀

一個事務讀取了另一個未提交的併發事務寫入的資料。（同上面的情景，未提交的資料被視為“髒”）。

模型

危害

假設 T1 修改行後，T2 讀取了它，接著 T1 回滾了。現在 T2 就持有了“不存在"的一行資料。基於不存在的資料對未來做決策是不正確的。

髒讀，也為違反約束大開方便之門。假設存在約束 x=y。接著假設 T1 同時將 x 和 y 的值增加 100，T2 同時將值翻倍。任何一個事務單獨執行時都能保證 x=y。但髒讀 w1[x += 100], w2[x *= 2], w2[y *= 2], w1[y += 100] 違反了約束。

最後，即使沒有對併發事務進行回滾，在另一個事務進行中間操作時啟動的事務也會因為髒讀從而造成資料庫狀態不一致。我們希望事務啟動時處於一個一致的狀態。

合理用法

當一個事務需要追蹤另一個事務時，髒讀是有用的，例如除錯和進度監控。也比如，當有一個事務在插入資料時再開一個事務反覆執行 COUNT(*) 以獲取插入速度／進度，但這也僅適用於髒讀不產生危害時。

此外，髒讀這種現象不會發生在對早已不再變動的歷史資訊進行查詢的時候。沒有新的寫入就不會產生問題。

不可重複讀，不對稱讀

事務讀取它先前已讀取過的資料時，發現它已經被另一個事務更改了（在初次讀操作之後有發生提交）。

注意，這不同於髒讀，因為另外的事務進行過提交。此外這種現象需要兩次讀取才會顯現。

模型

上面的過程涉及到兩個值時稱作不對稱讀：

不可重複讀是一種特殊形式的讀傾斜：b=a

危害

如同髒讀，不可重複讀允許一個事務讀取一個不一致的狀態。它發生的方式稍微不同。假設存在約束 x=y。

• start, x = y = 0
• r1[x] … w2[x=1] … w2[y=1] … c2 … r1[y]
• 從 T1 的視角看 x = 0 ≠ 1 = y

T1 至始至終沒有讀取任何髒資料，但讀取過程中 T2 插入，改變一些值，並在 T1 再次讀取前進行了提交。注意這個違規操作甚至不要求 T1 重新讀取相同的值。

不對稱讀可能造成兩個相關元素之間的約束被破壞。例如，假設存在約束 x+y > 0，且：

• start, x = y = 50
• r1[x] … r1[y] … r2[x] … r2[y] … w1[y=-40] … w2[x=-40] … c1 … c2
• T1 和 T2 各自觀察到 x+y=10，但它們一起提交後導致 x y 的和為 -80。

另一個涉及到兩個值的違法約束的情況出現在外來鍵和其目標之間。不對稱讀會讓它們混亂。例如，T1 從一個與表 B 相關聯的表 A 中讀取了一行，但是 T2 從表 B 中刪除了該行並進行了提交，這造成表 A 覺得行仍存於表 B 但卻無法讀取到它。

當備份資料庫的同時執行事務將是災難性的，因為觀察到的狀態可能不一致，將造成無法執行還原。

合理用法

非可重複讀允許訪問最新提交的資料。這可能在對大資料（或經常重複資料）進行聚合報告時有用，因為它們可以容忍讀操作時短暫地違反約束。

幻讀

事務再次執行返回一組滿足搜尋條件的行的查詢時，發現滿足該條件的行的集合由於另一個剛剛提交的事務而發生了更改。

幻讀類似於不可重複讀，但幻讀發生的條件是其匹配查詢條件的集合改變了，而不是單條資料。

模型

危害

有一種情況是，當一個表包含代表資源分配的行（如僱員和他們的工資）時，其中一個事務作為“調控者”會增加每行代表的資源，而另一個事務會插入新行。幻讀會包含新行，使調控者預算超標。

再舉一個相關例子。考慮這樣一個約束：它要求一系列工作任務排單後總時長不能超過 8 小時。T1 讀取了排單，發現總時長只有 7 個小時，於是它新增了一個時長 1 小時的新任務，同時併發事務 T2 也做了同樣的事情。

合理用法

分頁查詢結果中的新返回頁面包含新新增條目就很合適。同樣，插入或刪除專案後使用者翻頁時商品條目能自動調整。

更新丟失

T1 讀取了一條資料，同時 T2 更新了這條資料。T1 根據讀取的內容也更新了這條資料，然後提交。T2 進行的更新丟失了。

模型

危害

在某些方面，這幾乎感覺不到異常。這並不會違反資料庫約束，因為更新丟失只是造成一些工作沒有提交而已。這種情況與應用程式連續對同一個值進行兩次提交類似。

然而，這畢竟是一個異常，任何其他事務都沒有機會看到更新，而且 T2 的提交行為變得像回滾一樣。但一批命令序列執行時有些命令可能觀察到變化，至少它們在檢查值的時候可以。

在真實世界裡，應用程式在執行讀和寫操作時，丟失更新會造成特別惡劣的影響。

例如，同時有兩人試圖購買某個活動剩下的最後一張入場券，這觸發了兩個事務，事務讀取到還剩下一張未賣出的票。應用程式在單獨執行緒中生成可列印票據並將其加入郵件佇列，同時修改剩餘票數為 0。在兩個更新同時完成後，剩餘票數為 0，這是正確的。然而有一個客戶收到的郵件中的票據是重複的。

最後，請注意，當應用程式（通常通過 ORM）更新行中的所有列，而不僅僅是那些自讀取後才更改的列時，丟失更新的風險會增加。

合理用法

在像 UPDATE foo SET bar = bar + 1 WHERE id = 123; 這樣的原子讀取並更新語句中，更新丟失是不會發生的，因為其它事務不能在 bar 的值讀取和更新之間執行寫操作。這種現象發生在應用程式讀取條目，內部對它進行計算，接著寫入新值的過程中間。我們之後會深入分析。

有時候應用程式在歷史更新中丟失一些值是可以接受的。感測器頻繁的覆蓋它通過多執行緒度量到的值，我們也只需要讀取它最近記錄的有意義的值。這種情況下，雖然略有做作，但可以容忍更新丟失。

不對稱寫

兩個併發事務讀取對方正在寫入的資料集來確定它們寫入的內容。

模型

注意，如果 b=a 那麼上述情況就變成了更新丟失。

危害

不對稱寫造成事務歷史不可序列化。回想一下，這意味著一個接著一個執行事務得到的結果沒有辦法與交錯執行時相同。

我見過的最明顯的例子是黑白行。照搬 PostgreSQL 維基文件：有下面一種情況，一些行包含一個顏色列，它的值或是“黑”或是“白”。有兩個使用者同時試圖將所有行的顏色變得一致，但是它們嘗試的方向卻是反的。一個使用者試圖將所有行的顏色變為黑色，另一個使用者試圖將所有行的顏色變為白色。

如果這些更新是序列執行的，所有的顏色最終會變得一致。然而如果沒有任何資料庫保護措施，交錯更新將簡單的相互逆轉，留下一堆混合的顏色。

不對稱寫也會打破約束。假設我們要求 x + y ≥ 0。且

• start, x = y = 100
• r1[x] … r1[y] … r2[x] … r2[y] … w1[y=-y] … w2[x=-x]
• now x+y = -200

兩個事務都讀到 x 和 y 的值是 100，所以對單個事務來說將某個值變為負數是可以的，得到的和仍然是非負數。然而它們同時將值變為負導致 x+y=-200，這違反了約束。想要感性的理解的話可以類比銀行賬戶，銀行賬戶的賬戶收支可以為負數，只要總的餘額保持非負數。

只讀序列異常

事務可以看到更新了的用來指示批處理已完成的控制記錄，但未看到其中一個記錄著批處理邏輯部分的詳細記錄，因為它讀取的是早期的控制記錄修訂。

前面列舉的異常只需要兩個併發事務就能產生，但是這個需要是三個。它在 2004 年被發現後就一直引人注意，因為它揭示了快照隔離級別（稍後討論）的缺陷，且它是唯一一個在不執行寫入的三個事務的執行中表現出來的異常。

模型

事務競爭進行如下三件事，

• T1: 為當前批處理生成報告
• T2: 為當前批處理新增新的任務
• T3: 將新的批處理啟用成“當前”

危害

歷史證明上述異常不可序列化。順序執行事務帶來不變性，即在生成報告的事務顯示了特定批次的總數之後，後續事務不能更改總數。

資料庫一致性保持完好，這種異常，僅導致報告的結論是不正確的。

合理用法

鑑於直到 2004 年才有人注意到這種現象，它不太可能像其它現象那樣容易引發問題。儘管它在任何時候都不該出現，但它也不是很嚴重。

其它？

我們已經羅列了所有可能出現的事務異常現象嗎？這很難知道；ANSI SQL-92 標準表示它們已經列出了所有異常：髒讀，不可重複讀，幻讀。直到 1995 年，貝倫森等人才發現其他序列異常，只讀異常直到 2004 年被指出。

第一個關聯式資料庫使用鎖來管理併發。SQL 標準用事務現象而不是鎖來描述問題，它允許基於非鎖的策略來實現標準。然而，標準作者未能發現其他異常的原因是因為他們發現的三個異常都是“偽裝的鎖”。

我不知道是否還有更多的沒有列出的事務異常現象，但似乎很有可能有。現在有眾多論文在研究可序列性本身的性質，因為它看起來像理論基礎。

隔離級別

商業資料庫通過一系列隔離級別實現併發控制，這些隔離級別實際上是受控的反序列。應用程式為了獲得較高的效能通常選擇較低的隔離級別。高的隔離級別意味著更好的事務執行效率和更短的事務平均響應時間。

如果你理解了上一節中“動物園”中的併發問題，那麼你也充分地睿智地理解了如何為應用程式選擇正確的隔離級別了。這裡需要深入理解的不是隔離級別如何防止了異常現象，而是隔離級別阻止了什麼異常現象。

在最頂部，序列化時任何異常現象都不會發生。隨著箭頭，阻止標記著的異常發生的保護邏輯被移除。

藍色的三個節點表示的隔離級別被 PostgreSQL 提供。令人費勁的地方是 SQL 規範提供的隔離級別數不足，PostgreSQL 將這些規範中的定義的隔離級別對映到它實際支援的隔離級別。

你需要的	你得到的
序列化	序列化
可重複讀	快照隔離
讀已提交	提已提交
讀未提交	讀已提交

例如：

BEGINISOLATIONLEVEL REPEATABLE READ;

-- 現在我們進入快照隔離

讀已提交是預設的隔離級別，現在想象一下，如果你現有的應用程式沒有采取預防措施，你可能遇到的併發問題。

樂觀 vs 悲觀

正如前面所提到的，我們不必深入瞭解每一個 PostgreSQL 隔離級別可以防止哪些並發現象，但是我們需要了解兩種一般性方法：樂觀和悲觀併發控制。這是因為每一種方法對應不同的應用程式設計技術要求。

悲觀併發控制將對資料庫行進行鎖定，以強制事務等待其執行讀寫操作的時機。因為它總是需要時間來獲取和釋放鎖，沮喪地假設會有衝突，所以叫做“悲觀”。

樂觀控制不會佔用鎖，它只是為每個事務生成單獨的資料庫當前狀態快照，觀察可能發生的衝突。如果一個事務干擾了另一個事務，資料庫將阻止造成干擾的事務並清除它完成的作業。這種方式是有效的，因為干擾其實很少見。

遇到衝突的數量取決於以下幾個因素：

• 對單個行的競爭。如果試圖更新同一行的事務的數量增加，造成衝突的可能性會變大。
• 隔離級別為不可重複讀時讀取了多行。讀的行越多，併發事務可能更新同樣的行的機會越大。
• 隔離級別在阻止幻讀級別時的掃描範圍尺寸。掃描的範圍越多，併發事務遭遇幻行的可能性越大。

在 PostgreSQL 中，有兩種使用樂觀併發控制的隔離級別：可重複讀（實際就是快照隔離）和可序列化。這些隔離級別並不是萬能藥，撒在不安全的應用程式上，就能解決所有的問題。使用它們需要修改應用邏輯。

在構建一個與使用隔離級別由樂觀併發控制的 PostgreSQL 互動的應用程式時必須小心。要知道任何變更在提交前都是不確定的，所有作業在一瞬時都可能被抹除。應用程式必須時刻準備著，如果檢查到查詢返回錯誤 40001 （代表 serialization_failure），就要重新執行事務。通用，應用程式在這種事務中不應該執行不可逆的真實操作。應用程式必須使用悲觀鎖來包含這種行為，或者在收到成功提交的結果後再執行操作。

你可能覺得可以在一個 PL/pgSQL 函式中快取序列化異常並執行重試，可惜重試不能在那兒執行。整個函式執行在一個事務內部, 在呼叫前就失去了對執行的控制。不幸的的是在提交的時刻發生序列錯誤的可能性最大，而對於函式來說，已經來不及進行捕捉了。

重試必須由資料庫客戶端進行控制。許多語言提供了幫助函式來處理類似任務。這兒列舉了一些。

• Haskell: hasql-transaction 自動重試並且在禁止任何不可重複的副作用的 Monad 下執行事務
• Python: psycopg2 how to retry
• Ruby: auto-retrying in sequel or the transaction_retry gem

因為重新生成事務很浪費，所以最好在有限的時間記憶體儲事務已避免作業丟失。

低隔離級別補償

一般來說，最好使用合適的隔離級別，以避免異常和查詢的擾亂。最好讓資料庫做它最擅長的。然而，如果你確定某些異常不會發生在您的使用場景，你可以選擇使用一個包含悲觀鎖的低隔離級別。

例如我們可以在讀和更新之間新增一個鎖來避免讀提交事務的更新丟失。這隻需要我們在選擇類語句中新增 "For Update"。

BEGIN;

SELECT *
  FROM player
 WHERE id = 42
  FOR UPDATE;

-- 一些遊戲邏輯

UPDATE player
  SET score = 853
WHERE id = 42;

COMMIT;複製程式碼

任何試圖選擇同樣的行進行更新的事務都會阻塞，直到第一個事務完成操作為止。這個使用選擇的更新技巧甚至可以在序列事務中被用來避免序列錯誤導致的重試，特別是你本打算在應用程式中採取非冪操作時。

最後，你可以冒著計算不準的風險使用較低的隔離級別。快照隔離級別被採用的一個主要原因是它比序列有更好的效能，同時還避免了大部分序列化能避免的併發異常。如果在你的場景中不會發生不對稱讀，你可以降低隔離級別使用快照。

引用的源和進一步閱讀

感謝那些為我寫的這篇文章提供建議的人。

• 在 Freenode IRC 頻道 #postgresql 上：Andrew Gierth (RhodiumToad) and Vik Fearing (xocolatl)
• 私人對話：Marco Slot，Andres Freund，Samay Sharma，和來自 Citus Data 的 Daniel Farina

進一步的閱讀

• Joe Celko’s SQL for Smarties, chapter 2
• A Critique of ANSI SQL Isolation Levels
• Transaction Isolation in PostgreSQL docs
• A Read-Only Transaction Anomaly Under Snapshot Isolation
• Serializable Snapshot Isolation in PostgreSQL
• Data Consistency Checks at the Application Level in the PostgreSQL docs
• The Transaction Concept: Virtues and Limitations

---

掘金翻譯計劃 是一個翻譯優質網際網路技術文章的社群，文章來源為 掘金 上的英文分享文章。內容覆蓋 Android、iOS、React、前端、後端、產品、設計 等領域，想要檢視更多優質譯文請持續關注 掘金翻譯計劃、官方微博、知乎專欄。