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

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

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

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

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

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

接下來(lái)，我們將詳細(xì)討論這些類(lèi)別。

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

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

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

原子性對(duì)比

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

自動(dòng)提交

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

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

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

語(yǔ)句級(jí)回滾

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

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

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

事務(wù)性DDL

在 Oracle 數(shù)據(jù)庫(kù)中，任何 DDL[4] 語(yǔ)句會(huì)自動(dòng)執(zhí)行 COMMIT，因此 無(wú)法回滾 DDL 語(yǔ)句。

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

一致性對(duì)比

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

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

主鍵和唯一約束在 Oracle 和 PostgreSQL 中的驗(yàn)證時(shí)機(jī)

以下 SQL 腳本在 Oracle 中不會(huì)報(bào)錯(cuò)：

圖片

在 PostgreSQL 中，同樣的腳本會(huì)報(bào)錯(cuò)：

圖片

原因在于，PostgreSQL 默認(rèn)在每行變化時(shí)檢查約束（不同于SQL標(biāo)準(zhǔn)），而 Oracle 在語(yǔ)句結(jié)束時(shí)檢查約束。 不過(guò)這個(gè)問(wèn)題可以通過(guò)將約束創(chuàng)建為 DEFERRABLE 來(lái)解決，這樣 PostgreSQL 會(huì)在語(yǔ)句結(jié)束時(shí)檢查約束，并與 Oracle 的行為保持一致。

隔離性對(duì)比

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

事務(wù)隔離級(jí)別的對(duì)比

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

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

圖片

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

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

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

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

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

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

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

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

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

持久性對(duì)比

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

其他事務(wù)差異

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

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

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

長(zhǎng)時(shí)間運(yùn)行的事務(wù)在任何關(guān)系型數(shù)據(jù)庫(kù)中都是一個(gè)問(wèn)題，因?yàn)樗鼈儠?huì)占用鎖并增加阻塞其他會(huì)話的幾率，長(zhǎng)事務(wù)也更容易遭遇死鎖。 在 PostgreSQL 中，長(zhǎng)事務(wù)會(huì)比 Oracle 更加棘手一些，因?yàn)樗鼈冞€會(huì)阻塞“自動(dòng)清理”（autovacuum）任務(wù)的進(jìn)程，從而導(dǎo)致表膨脹，治理起來(lái)要費(fèi)些事。

SELECT ... FOR UPDATE 的對(duì)比

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

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

事務(wù)ID回卷

事務(wù)ID回卷[6] 只在 PostgreSQL 中存在。 PostgreSQL 的多版本控制通過(guò)在每一行中存儲(chǔ) 事務(wù)ID[7] 來(lái)管理行版本的可見(jiàn)性。

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

結(jié)論

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

References

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

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

[3]子事務(wù)實(shí)現(xiàn)的，可能會(huì)嚴(yá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