深藏若虛

PostgreSQL 與 MySQL 關於 transaction 的設計理念

Transaction Design of PostgreSQL & MySQL

近日因為想要跳槽到 PostgreSQL,所以開始閱讀官方的參考文件。在讀到 3.4 Transactions 時,其中有一段不太能理解:

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

因為以往對 transaction1 的了解不深,一直以為只會用在「我有數個 SQL 敘述,希望能夠其中一個失敗時,取消其他 SQL 敘述的執行」的情況,所以不太能理解「每個 SQL 敘述預設帶有一個 transaction」的設計,非常疑惑為什麼會需要將單個 SQL 敘述也用 transaction 包起來。所以就去請教了對 PostgreSQL 有經驗的 @david50407。本篇文章大概就是在講述敝人向其請教後的理解。

基本觀念

PostgreSQL 參考手冊在 3.4 Transactions 的第一段,就以這樣的文字來敘述:

The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

該文字的意思大概就是,transaction 可以把 PostgeSQL 裡面多個敘述併成一個,並且遵從「全或無」(all-or-nothing)2的操作;也就是若在併成一個操作裡的諸多敘述,若有其中一個敘述出錯,其他成功的敘述也會被撤銷,就等同全部都沒有執行。

這個功能比較常用的地方就在於金融業,常見的例子就是要避免 A 轉帳給 B 時,不能發生「扣了 A 的錢,但 B 那邊卻沒有增加」或是「對 A 扣錢失敗,但是 B 那邊的錢卻增加了」的情況,一定是要「扣了 A 的錢,也要加 B 的錢」,不然就是都不要發生。

在沒有特別處理的情況下,SQL 敘述大致如下:

程式碼 1-1:在沒有使用 transaction 的銀行交易情境示例

UPDATE accounts SET deposit = deposit - 30000.00 WHERE name = 'A';
UPDATE accounts SET deposit = deposit + 30000.00 WHERE name = 'B';

但若因為某種原因導致第一個敘述失敗,第二個敘述卻成功了,那麼銀行就會損失三萬元。若第一個敘述成功了,第二個敘述失敗,那顧客就會平白無故損失三萬元,導致銀行信譽掃地。所以在處理這類需要「全或無」的操作時,就必須將這些敘述宣告成一個 transaction block1

transaction block 的宣告方式,就是使用 BEGIN;COMMIT; 兩個敘述,將多個需要綁定的 SQL 敘述給包起來,以上面的案例來改進,大致如下:

程式碼 1-2:使用 transaction 的銀行交易情境示例

BEGIN;
UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT;

若 transaction block 圍起來的敘述,只要有其中一個不成立,那麼 transaction block 裡的敘述就都不會被執行3

PostgreSQL 關於 transaction 的設計理念

回到原本的疑問,在解惑之前先闡述一個 PostgreSQL 的基本概念:

PostgreSQL 一次只會執行一個原子級操作(atomic operation,意味這該項操作不可分割),PostgreSQL 只會在完成一次原子級操作才會將結果從記憶體寫入檔案,若是中間有任何衝突,就會重新從檔案讀取資料回記憶體覆蓋失敗的部分、進行還原。

講到這邊,大概就能有點感覺。是的,transaction 就是 PostgreSQL 的原子級操作。

在沒有自行加入 BEGIN;COMMIT; 語法的情況下,PostgreSQL 是不會把結果從記憶體存回硬碟,但是為了方便操作,PostgreSQL 預設會把沒有在 transaction block 中的 SQL 敘述外自動加入BEGIN;COMMIT;,也就是將該則敘述獨立視為一個 transaction block。如此才不用在編寫 SQL 敘述時,還有手動加上一對冗贅的 transaction 語法。

程式碼 1-1 為例,雖然我們沒有幫他加上 transaction 語法,但是事實上它在執行時,是已經被 PostgreSQL 加入語法了,其真實的樣貌如下(程式碼 2-1):

程式碼 2-1:PostgreSQL transaction 預設行為示例

-- 本段程式碼的 BEGIN; 和 COMMIT; 都是 PostgreSQL 自動加的
BEGIN;  
UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
COMMIT;
BEGIN;
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT;

所以每項敘述都會是一個原子級操作,都被視為一個 transaction block,在成功完成後,才會寫入硬碟。若失敗則會跳下一個敘述繼續執行(這邊是假設匯入 SQL檔的情境,其他情境要視失敗時對例外地處理決策決定是中止操作還是繼續執行)。

從上面透過設計理念的理解,原本的問題也就豁然開朗。了解其背後理念和原因後,也可以繼續思考以下問題,讓自己對這份知識更加熟稔:

問題一

匯入一個 SQL 文件 dump.sql 到 PostgreSQL 時,假設 dump.sql 裡沒有任何 transaction 語法,並有 N 行敘述,而第 M 行敘述是會發生衝突的,那資料庫最後會有幾筆異動?
(A) N 筆(成功執行所有敘述)
(B) N – 1 筆(成功執行除了第 M 行以外的敘述)
(C) M – 1 筆(只成功執行第 M 行之前的敘述,第 M 行及其之後的敘述都被駁回)
(D) 0 筆(全部敘述遭到駁回)

問題二

承問題一,若答案不是 (D) 選項,那為什麼在一起匯入的情況下,不是所有敘述都被駁回,而是部分被套用?

問題三

承問題二,那該怎麼做,才能讓匯入 SQL 文件到 PostgreSQL 時,是一有失敗就全部駁回,而不是部分套用呢?

與 MySQL 比較

在略曉 PostgreSQL 關於 transaction 的設計後,在這節轉向探討 MySQL 關於 transaction 的設計。

MySQL 也是擁有 transaction 功能的,也定義了 BEGIN;4COMMIT 語法。它也會預設對沒有在 transaction block 中的 SQL 敘述,進行預設的行為,那到底是哪邊跟 PostgreSQL 不同呢?

MySQL 的設計理念是在於,遇到 COMMIT; 語法時,才會將資料從記憶體寫入硬碟,並且預設會對所有 SQL 敘述後自動在其後加上 COMMIT;,在 MySQL 稱這項預設行為為自動提交(auto commit)。以程式碼 1-1 為例,在 MySQL 中其真實的樣貌如下(程式碼 3-1):

程式碼 3-1:MySQL auto commit 行為的示例

-- 本段程式碼的 COMMIT; 都是 MySQL 自動加的
UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
COMMIT;
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT;

程式碼 2-1 進行比較後,會發現 MySQL 在預設行為上,只會在敘述後加入 COMMIT; 而不會在之前加入 BEGIN;,這邊就顯示出兩者在設計概念上的不同。PostgreSQL 是以一個 transaction block 為一個單位作為 transaction 的操作;而 MySQL 則是以當下的 COMMIT; 到上一個 COMMIT; 之間的敘述作為 transaction 的操作(準確來說,是指目前還沒被提交過的敘述)。而 MySQL 的 BEGIN; 就類似一個局部停用自動提交的語法,並作用到一個 COMMIT;ROLLBACK; 語法為止,如程式碼 3-2 所示。

程式碼 3-2:MySQL BEGIN & COMMIT 示例

BEGIN;
UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT;

若是要在當前連線全域關閉這個自動動提交(auto commit)5,可以透過 SET autocommit=0; 敘述,當 autocommit 設置為 0 時,就會停用自動提交。若將這功能停用時,就必須自己加入 COMMIT; 以將之前執行成功且尚未提交過的敘述存入硬碟。與 BEGIN; 不同,就算遇到 COMMIT;,自動提交仍會保持停用,直到將 autocommit 設置為 1 重啟這功能為止。同樣以程式碼 1-1 作為案例,大致如下(程式碼 3-3):

程式碼 3-3:MySQL autocommit 設置示例

SET autocommit=0;

UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'A';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'B';
COMMIT; -- COMMIT 並不會讓 autocommit 重新啟用

UPDATE accounts SET deposit = deposit - 100.00 WHERE name = 'C';
UPDATE accounts SET deposit = deposit + 100.00 WHERE name = 'D';
ROLLBACK; -- ROLLBACK 也不會讓 autocommit 重新啟用

SET autocommit=1; -- 只有將 autocommit 設置為 1 時,才會重啟自動提交的功能。

除了自動提交外,在 MySQL 參考手冊的 13.3.3 Statements That Cause an Implicit Commit 有提到,當遇到修改資料表的敘述、資料定義語言(DDL)、或是 transactions 控制流程6和資料表鎖定相關的敘述時,在執行該敘述前,都會自動執行 COMMIT;,也就是所謂的隱性提交(implicitly commit)7。這部分可以參見程式碼 3-4 的示例加深理解。

程式碼 3-4:MySQL 隱性提交示例

SET autocommit=0; -- 先停用自動提交

INSERT INTO log (content) VALUES ('A');

BEGIN; -- 執行此行敘述前,會進行隱性提交,導致前面的 SQL 敘述被存入硬碟。
INSERT INTO log (content) VALUES ('B');
COMMIT;

INSERT INTO log (content) VALUES ('C');

SET autocommit=1; -- 執行此行敘述前,會進行隱性提交,導致前面的 SQL 敘述被存入硬碟。

這也導致了,MySQL 不能與 PostgreSQL 一般,進行 transactions 的嵌套(nested)8,因為若在 transactions 裡又輸入 BEGIN; 時,這之前的 SQL 敘述就會先被 COMMIT; 存入硬碟中。以下面程式碼(程式碼 3-5)為例,在 MySQL 中,First 是會被存入資料表的,PostgreSQL 則不會。

程式碼 3-5:transactions 嵌套示例

CREATE TABLE log (
    content varchar(255)
);

BEGIN;
    INSERT INTO log (content) VALUES ('First');

    BEGIN;
        INSERT INTO log (content) VALUES ('Second');
        ROLLBACK;
    COMMIT;

    ROLLBACK;
COMMIT;

延伸應用

另外,也因為 transaction 是在整個 transaction 完成且成功的情況下才會將資料寫入硬碟,不會如同一般情況是一個敘述寫入一次(事實上,一個敘述也等同一個 transaction。),所以若遇到需要執行大量敘述時,可以透過 transaction block 包覆起來,讓全部執行完後再一次寫入硬碟,節省對硬碟做 I/O 的時間,讓執行更加快。如程式碼 4-1

程式碼 4-1:大量敘述搭配 transaction 以提升效率的示例

BEGIN;
INSERT INTO users (username, password) VALUES ('USERNAME_1', 'PASSWORD_1');
INSERT INTO users (username, password) VALUES ('USERNAME_2', 'PASSWORD_2');
-- ...
INSERT INTO users (username, password) VALUES ('USERNAME_1000', 'PASSWORD_1000');
COMMIT;

結論

本文透過對 PostgreSQL 對每個單一敘述都使用 transaction block 包覆起來的設計,以及 transaction 代表從記憶體寫入硬碟這件事的認知,總算一解原先對該段敘述的疑惑。也透過比較 MySQL 相對應的功能的設計,更深入了解 transaction 運作上的差異。有了這些概念,日後在兩套資料庫系統交互使用時,就能避免踩到地雷了。

最後,也感謝 @david50407 對這相關議題的講解以及 @JasonWu 的共同討論。


  1. transaction 通常在中文會翻譯成「交易」,但為了突顯其為本文探討的核心,在本文就不翻譯了。transaction block 亦同。 
  2. all-or-nothing 直譯就是「全有或全無」,社群多是直接使用原文為主。另外搜尋到的中山資管所資料庫系統專題課程講義裡面則是加註「完整的執行完畢或保持原狀都不做」。這邊為了突顯其專有名詞的意涵,刻意翻成比較文言的「全或無」,其他類似翻譯可以參照國家研究院的雙語詞彙資訊網;另有「悉無律」的翻譯,但這個詞似乎比較常作為漢字在日文中使用。這方面的翻譯多用在生物學的神經衝動現象上。(我和朋友都戲稱這叫老鼠屎原則,因為只要有敘述裡如有一顆老鼠屎的錯誤發生,就得將代表整鍋粥的 transaction block 全部丟掉。比較風雅一點的戲稱就是「鼠糞原則」。) 
  3. PostgreSQL 在 transaction block 中,若遇到錯誤,會自動丟出 ROLLBACK;。 
  4. MySQL 預設是使用 SQL 標準的 START TRANSACTION;作為 transaction 起始的宣告語法,但也支援 BEGIN; 作為同義語法。 
  5. MySQL 在當前連線停用自動提交功能,並不會導致其他連線的自動提交也被停用。 
  6. MySQL 會造成隱性提交(implicit commit)的語法:BEGIN, LOCK TABLES, SET autocommit = 1 (若原本不等於 1 時), START TRANSACTION, UNLOCK TABLES
  7. MySQL Ref 13.3.3: The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement. 
  8. MySQL Ref 13.3.3: Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms. 

Information Technology , ,