事務(wù)系統(tǒng)是關(guān)系型數(shù)據(jù)庫的核心組成部分，在應(yīng)用開發(fā)中，為確保 數(shù)據(jù)完整性 提供了重要支持。 SQL 標準規(guī)范了數(shù)據(jù)庫事務(wù)的一些功能，但并未明確規(guī)定許多細節(jié)。因此，關(guān)系型數(shù)據(jù)庫的事務(wù)系統(tǒng)可能存在顯著差異。

近年來，許多人嘗試從 Oracle 數(shù)據(jù)庫遷移到 PostgreSQL。為了順利將應(yīng)用從 Oracle 遷移到 PostgreSQL，理解兩者事務(wù)系統(tǒng)之間的差異至關(guān)重要。 否則，您可能會遇到一些令人頭痛的意外情況，危及到性能和數(shù)據(jù)完整性。所以，我認為有必要編寫一篇文章，對比 Oracle 和 PostgreSQL 事務(wù)系統(tǒng)的特性。

作者：Laurenz Albe ，譯者：馮若航

ACID：數(shù)據(jù)庫事務(wù)提供的服務(wù)

這里的 ACID 不是什么化學或藥品術(shù)語，而是以下四個詞的首字母縮寫：

?Atomicity（原子性）：保證在單個數(shù)據(jù)庫事務(wù)中，所有語句作為一個整體執(zhí)行，要么全部成功，要么全部不生效。這應(yīng)涵蓋所有類型的問題，包括硬件故障。?Consistency（一致性）：保證任何數(shù)據(jù)庫事務(wù)都不會違反數(shù)據(jù)庫中定義的約束。?Isolation（隔離性）：保證并發(fā)運行的事務(wù)不會導致某些“異?！保磾?shù)據(jù)庫中一些不可由串行執(zhí)行的事務(wù)產(chǎn)生的可見狀態(tài)）。?Durability（持久性）：保證一旦數(shù)據(jù)庫事務(wù)提交（完成），即使發(fā)生系統(tǒng)崩潰或硬件故障，事務(wù)也無法被撤銷。

接下來，我們將詳細討論這些類別。

Oracle 與 PostgreSQL 事務(wù)的相似之處

首先，描述一下 Oracle 和 PostgreSQL 在事務(wù)管理中相同的部分是有幫助的。幸運的是，許多重要的特性都屬于這一類：

?兩個數(shù)據(jù)庫系統(tǒng)都使用多版本并發(fā)控制（MVCC）：讀取和寫入操作互不阻塞。讀取操作會讀取舊數(shù)據(jù)，而在更新或刪除事務(wù)進行時，不會阻塞讀取。?兩個數(shù)據(jù)庫系統(tǒng)都在事務(wù)結(jié)束前保持鎖定。?兩個數(shù)據(jù)庫系統(tǒng)都將 行鎖[1] 保存在行本身，而不是在鎖表中。因此，鎖定一行可能會導致額外的磁盤寫入，但不需要進行 鎖升級。?兩個數(shù)據(jù)庫系統(tǒng)都支持 SELECT ... FOR UPDATE 進行顯式的并發(fā)控制。更多關(guān)于差異的討論，后面會說。?兩個數(shù)據(jù)庫系統(tǒng)都使用 READ COMMITTED 作為默認的事務(wù)隔離級別，這在兩個系統(tǒng)中的行為非常相似。

原子性對比

在這兩個數(shù)據(jù)庫中，原子性有一些微妙的差異：

自動提交

在 Oracle 中，任何 DML[2] 語句會隱式啟動一個數(shù)據(jù)庫事務(wù)，除非已經(jīng)有一個事務(wù)處于開啟狀態(tài)。您必須顯式地使用 COMMIT 或 ROLLBACK 來結(jié)束這些事務(wù)。沒有特定的語句來啟動一個事務(wù)。

而 PostgreSQL 則處于 自動提交模式：除非您顯式啟動一個多語句事務(wù)（通過 START TRANSACTION 或 BEGIN），每個語句都會在自己的事務(wù)中運行。在此類單語句事務(wù)結(jié)束時，PostgreSQL 會自動執(zhí)行 COMMIT。

許多數(shù)據(jù)庫 API 允許您關(guān)閉自動提交。由于 PostgreSQL 服務(wù)器不支持禁用自動提交，客戶端通過適當?shù)臅r候自動發(fā)送 BEGIN 來模擬這一點。使用這樣的 API，您無需擔心這種差異。

語句級回滾

在 Oracle 中，導致錯誤的 SQL 語句不會中止事務(wù)。相反，Oracle 會回滾失敗語句的效果，事務(wù)仍然可以繼續(xù)。要回滾整個事務(wù)，您需要處理錯誤并主動調(diào)用 ROLLBACK。

而在 PostgreSQL 中，如果事務(wù)中的 SQL 語句發(fā)生錯誤，整個事務(wù)會被中止。直到您使用 ROLLBACK 或 COMMIT（兩者都會回滾事務(wù)）結(jié)束事務(wù)時，所有后續(xù)的語句都會被忽略。

大多數(shù)編寫良好的應(yīng)用程序不會遇到這個差異的問題，因為通常情況下，當發(fā)生錯誤時，您會希望回滾整個事務(wù)。 然而，PostgreSQL 的這種行為在某些特定情況下可能會令人煩惱：想象一個長時間運行的批處理任務(wù)，其中壞數(shù)據(jù)可能會導致錯誤。 您可能希望能夠處理錯誤，而不是回滾已經(jīng)完成的所有操作。在這種情況下，您應(yīng)該在 PostgreSQL 中使用（符合 SQL 標準的）保存點。 請注意，您應(yīng)謹慎使用保存點：它們是通過 子事務(wù)實現(xiàn)的，可能會嚴重影響性能[3]。

事務(wù)性DDL

在 Oracle 數(shù)據(jù)庫中，任何 DDL[4] 語句會自動執(zhí)行 COMMIT，因此 無法回滾 DDL 語句。

在 PostgreSQL 中則沒有這種限制。除了少數(shù)例外（如 VACUUM、CREATE DATABASE、CREATE INDEX CONCURRENTLY等），您可以 回滾任何 SQL 語句。

一致性對比

在這一領(lǐng)域，Oracle 和 PostgreSQL 之間差異不大；兩者都會確保事務(wù)不違反約束。

或許值得一提的是，Oracle 允許您使用 ALTER TABLE 啟用或禁用約束。例如，您可以禁用約束，執(zhí)行違反約束的數(shù)據(jù)修改操作，然后使用 ENABLE NOVALIDATE 啟用約束（對于主鍵和唯一約束，只有在它們是 DEFERRABLE 時才有效）。 而在 PostgreSQL 中，只有超級用戶才能禁用實現(xiàn)外鍵約束以及可推遲唯一和主鍵約束的觸發(fā)器。設(shè)置 session_replication_role = replica 也是一個禁用此類觸發(fā)器的方式，但同樣需要超級用戶權(quán)限。

主鍵和唯一約束在 Oracle 和 PostgreSQL 中的驗證時機

以下 SQL 腳本在 Oracle 中不會報錯：

圖片

在 PostgreSQL 中，同樣的腳本會報錯：

圖片

原因在于，PostgreSQL 默認在每行變化時檢查約束（不同于SQL標準），而 Oracle 在語句結(jié)束時檢查約束。 不過這個問題可以通過將約束創(chuàng)建為 DEFERRABLE 來解決，這樣 PostgreSQL 會在語句結(jié)束時檢查約束，并與 Oracle 的行為保持一致。

隔離性對比

這是 Oracle 和 PostgreSQL 差異最明顯的領(lǐng)域。Oracle 對事務(wù)隔離的支持相對有限。

事務(wù)隔離級別的對比

SQL 標準定義了四個事務(wù)隔離級別：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。 但與標準的詳細程度相比，單獨的級別定義得比較模糊。例如，標準提到，“臟讀”（讀取其他事務(wù)未提交的數(shù)據(jù)）在 READ UNCOMMITTED 隔離級別下是“可能”的，但并沒有明確指出這是否為必需。

Oracle 只提供 READ COMMITTED 和 SERIALIZABLE 隔離級別。然而后者其實并不完全準確；Oracle 提供的是快照隔離。例如，以下并發(fā)事務(wù)均會成功（第二個會話如下所示）：

圖片

如果這些事務(wù)串行執(zhí)行，第二個事務(wù)的結(jié)果應(yīng)該是 count 為 1。

除了不準確，Oracle 的實現(xiàn)還存在許多問題。例如，如果您創(chuàng)建一個表時未指定 SEGMENT CREATION IMMEDIATE，然后在 SERIALIZABLE 事務(wù)中嘗試插入第一行，就會遇到序列化錯誤。 雖然這在技術(shù)上是合法的，但如果在更高的隔離級別遇到問題時，Oracle 會經(jīng)常拋出序列化錯誤。

PostgreSQL 支持所有四個隔離級別，但它會默默地將 READ UNCOMMITTED 升級為 READ COMMITTED（這在 SQL 標準中可能并不符合要求）。 而 SERIALIZABLE 事務(wù)則是真正的串行化事務(wù)。PostgreSQL 的 REPEATABLE READ 行為類似于 Oracle 的 SERIALIZABLE，但實際上 PostgreSQL 的實現(xiàn)更好。

READ COMMITTED 級別下并發(fā)數(shù)據(jù)修改的對比

默認的事務(wù)隔離級別 READ COMMITTED 是一個低隔離級別，這意味著許多異常仍然可能發(fā)生。

我在之前的文章中描述了其中的一種異常：事務(wù)異常與 SELECT FOR UPDATE[5]。簡而言之，情況如下：

?一個事務(wù)修改了表中的一行，但尚未提交?第二個事務(wù)執(zhí)行了一個鎖定行的語句（例如 SELECT ... FOR UPDATE），并且掛起?第一個事務(wù)提交

在這種情況下，兩個數(shù)據(jù)庫系統(tǒng)會有什么結(jié)果？在 Oracle 和 PostgreSQL 中，您都能看到最新提交的數(shù)據(jù)，但細節(jié)有所不同：

?PostgreSQL 只重新評估被鎖定的行，操作較快，但可能會導致不一致的結(jié)果?Oracle 會 重新執(zhí)行完整查詢，盡管速度較慢，但能夠提供一致的結(jié)果

持久性對比

兩個數(shù)據(jù)庫系統(tǒng)都通過事務(wù)日志實現(xiàn)持久性（Oracle 中為“REDO 日志”，PostgreSQL 中為“WAL日志”）。在這一領(lǐng)域，Oracle 和 PostgreSQL 提供的保證是相同的。

其他事務(wù)差異

事務(wù)的大小和持續(xù)時間限制

這一領(lǐng)域的差異主要源于 Oracle 和 PostgreSQL 實現(xiàn)多版本并發(fā)控制（MVCC）的方式不同。Oracle 使用 UNDO 表空間 來存儲已修改行的舊版本，而 PostgreSQL 將多個版本的行存儲在表中。

由于這個原因，Oracle 事務(wù)中數(shù)據(jù)修改的數(shù)量受限于 UNDO 表空間的大小。對于大批量刪除或更新，Oracle 通常會采用分批處理并在每批之間執(zhí)行 COMMIT。 而在 PostgreSQL 中沒有這種限制，但大規(guī)模更新會導致表膨脹，因此您也可能希望分批更新，并在更新間運行 VACUUM。然而在 PostgreSQL 中，并沒有理由限制大批量刪除的規(guī)模。

長時間運行的事務(wù)在任何關(guān)系型數(shù)據(jù)庫中都是一個問題，因為它們會占用鎖并增加阻塞其他會話的幾率，長事務(wù)也更容易遭遇死鎖。 在 PostgreSQL 中，長事務(wù)會比 Oracle 更加棘手一些，因為它們還會阻塞“自動清理”（autovacuum）任務(wù)的進程，從而導致表膨脹，治理起來要費些事。

SELECT ... FOR UPDATE 的對比

兩個數(shù)據(jù)庫系統(tǒng)都知道這個命令，它用于同時讀取并鎖定一行。Oracle 和 PostgreSQL 都支持 NOWAIT 和 SKIP LOCKED 子句。 PostgreSQL 缺少 WAIT <integer> 子句，但是可以通過動態(tài)調(diào)整 lock_timeout 參數(shù)實現(xiàn)類似的功能。

這里最重要的區(qū)別在于，PostgreSQL 中如果你打算更新某一行，F(xiàn)OR UPDATE 并非 合適的語句 —— 除非你打算刪除某行或修改主鍵或唯一鍵列，否則正確的鎖定模式應(yīng)為 FOR NO KEY UPDATE。

事務(wù)ID回卷

事務(wù)ID回卷[6] 只在 PostgreSQL 中存在。 PostgreSQL 的多版本控制通過在每一行中存儲 事務(wù)ID[7] 來管理行版本的可見性。

這些編號來自一個 32 位整型計數(shù)器，最終會發(fā)生回卷。 所以 PostgreSQL 需要執(zhí)行維護操作（FREEZE）來避免出現(xiàn)事務(wù)ID回卷。在高事務(wù)量（TPS）的系統(tǒng)中，這可能成為一個需要特別關(guān)注和調(diào)整的問題。

結(jié)論

在大多數(shù)方面，Oracle 和 PostgreSQL 的事務(wù)行為非常相似。但它們之間確實存在差異，如果您計劃遷移到 PostgreSQL，了解這些差異是很重要的。本文中的對比有助于您在遷移過程中識別潛在的問題。

References

[1] 行鎖:https://www.cybertec-postgresql.com/en/row-locks-in-postgresql/

[2]DML:https://en.wikipedia.org/wiki/Data_manipulation_language

[3]子事務(wù)實現(xiàn)的，可能會嚴重影響性能:https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/

[4]DDL:https://en.wikipedia.org/wiki/Data_definition_language

[5]事務(wù)異常與SELECT FOR UPDATE:https://www.cybertec-postgresql.com/en/transaction-anomalies-with-select-for-update/

[6]事務(wù)ID回卷:https://www.cybertec-postgresql.com/en/autovacuum-wraparound-protection-in-postgresql/

[7]事務(wù)ID: https://www.postgresql.org/docs/current/transaction-id.html