事務系統是關系型數據庫的核心組成部分，在應用開發中，為確保 數據完整性 提供了重要支持。 SQL 標準規范了數據庫事務的一些功能，但并未明確規定許多細節。因此，關系型數據庫的事務系統可能存在顯著差異。

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

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

ACID：數據庫事務提供的服務

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

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

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

Oracle 與 PostgreSQL 事務的相似之處

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

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

原子性對比

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

自動提交

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

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

許多數據庫 API 允許您關閉自動提交。由于 PostgreSQL 服務器不支持禁用自動提交，客戶端通過適當的時候自動發送 BEGIN 來模擬這一點。使用這樣的 API，您無需擔心這種差異。

語句級回滾

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

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

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

事務性DDL

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

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

一致性對比

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

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

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

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

圖片

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

圖片

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

隔離性對比

這是 Oracle 和 PostgreSQL 差異最明顯的領域。Oracle 對事務隔離的支持相對有限。

事務隔離級別的對比

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

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

圖片

如果這些事務串行執行，第二個事務的結果應該是 count 為 1。

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

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

READ COMMITTED 級別下并發數據修改的對比

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

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

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

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

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

持久性對比

兩個數據庫系統都通過事務日志實現持久性（Oracle 中為“REDO 日志”，PostgreSQL 中為“WAL日志”）。在這一領域，Oracle 和 PostgreSQL 提供的保證是相同的。

其他事務差異

事務的大小和持續時間限制

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

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

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

SELECT ... FOR UPDATE 的對比

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

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

事務ID回卷

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

這些編號來自一個 32 位整型計數器，最終會發生回卷。 所以 PostgreSQL 需要執行維護操作（FREEZE）來避免出現事務ID回卷。在高事務量（TPS）的系統中，這可能成為一個需要特別關注和調整的問題。

結論

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

References

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

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

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

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

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

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

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