對(duì)比Oracle與PostgreSQL事務(wù)系統(tǒng),你學(xué)到了什么?
事務(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)橥ǔG闆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