原文地址：https://www.bytebase.com/blog/postgres-vs-mysql-ddl-transaction

數據庫 schema 變更作為關鍵操作，需要慎重規劃執行；因此，執行變更是否安全可靠，是選擇數據庫管理系統的關鍵考慮因素。接下來，我們將比較 PostgreSQL 17 和 MySQL 8 對於 DDL（Data Definition Language）事務的處理，且重點關注二者的原子性和回滾能力。

什麼是 DDL 事務

深入比較之前，讓我們先明確 DDL 事務的含義。DDL 語句可以組合在一起，作為一個單元提交，或者在出現問題時完全回滾。

有兩個重要的概念需要區分：

1. 事務性 DDL：能夠在多語句事務塊中包含 DDL 語句，並可以選擇一起提交或回滾所有語句。
2. 原子性 DDL：保證單個 DDL 語句是原子的（全有或全無），但不一定支持將其包含在多語句事務中。

PostgreSQL 17：真正的事務性 DDL

在 PostgreSQL 17 中，DDL 操作是完全事務性的，這意味着：

• DDL 語句可以與 DML 語句一起包含在事務塊中
• 多個 DDL 操作可以作為單個單元提交或回滾
• 保存點可以在包含 DDL 語句的事務中使用
• 如果事務失敗，所有 DDL 更改都會回滾，數據庫保持在原始狀態

此規則只有少數例外：對數據庫和表空間本身的操作（如 CREATE DATABASE 或 DROP TABLESPACE）無法回滾。除這些之外，所有其他目錄操作都是可逆的。

MySQL 8：原子性 DDL

在 MySQL 8 之前，MySQL 中的 DDL 操作根本不是原子的。如果 DDL 語句在執行過程中失敗（例如添加了多個列或索引的 ALTER TABLE 操作），數據庫可能會處於不一致的、部分修改的狀態。

MySQL 8 引入了一個稱為原子性 DDL 的功能，這相比以前的版本是一個重大改進，但與 PostgreSQL 的方法在根本上有所不同。

在 MySQL 8 中，DDL 在語句級別是原子的，這意味着：

• 單個 DDL 語句要麼完全完成，要麼完全回滾
• DDL 語句在執行前隱式提交任何活動事務
• DDL 語句不能成為可以回滾的多語句事務的一部分
• 崩潰恢復確保語句級別的原子性

MySQL 的原子性 DDL 通過 InnoDB 中的一個特殊內部 DDL_LOG 表實現，該表跟蹤 DDL 執行期間文件和結構的創建。此日誌在提交/回滾時用於正確清理，確保崩潰後不會殘留孤立文件或索引樹。

需要注意的是，原子性 DDL 僅在 MySQL 8 InnoDB 存儲引擎中受支持。對於使用其他存儲引擎的表，仍可能發生部分更新。

示例

為了更好地理解這些差異，讓我們運行一些 DDL，來演示 DDL 事務在兩個系統中的行為。

PostgreSQL 17

我們的第一個示例將所有 DDL 操作包裝在一個事務中。當我們執行 ROLLBACK 命令時，所有表和索引都會被徹底刪除。

-- 開始一個事務塊
BEGIN;
-- 創建一個簡單的表
CREATETABLEusers (
    id SERIALPRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 添加一個索引
CREATEINDEXidx_usernameON users(username);
-- 糟糕！我們犯了一個錯誤，想要回滾所有更改
ROLLBACK;
-- 驗證表未被創建
SELECT table_name FROMinformation_schema.tables
WHERE table_schema ='public'AND table_name ='users';
-- 應該返回無行，因為事務被回滾了

第二個示例演示了 PostgreSQL 如何用 SAVEPOINT 進行部分回滾，為開發者提供 schema 變更的細粒度控制。

BEGIN;
-- 創建一個表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 創建一個保存點
SAVEPOINT after_users_table;
-- 修改表以添加列
ALTER TABLE users ADD COLUMN email VARCHAR(100);
-- 糟糕！我們只想回滾列的添加
ROLLBACK TO after_users_table;
-- 改為添加不同的列
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT TRUE;
-- 提交事務
COMMIT;

MySQL 8

在這個 MySQL 示例中，儘管將 DDL 語句包裝在事務塊中，ROLLBACK 命令也並不會撤銷表的創建。這是因為每個 DDL 語句在執行前都會隱式提交事務，使得無法將多個 DDL 語句作為一個單元回滾。

-- 嘗試使用事務塊（注意：這對 DDL 不會按預期工作）
START TRANSACTION;
-- 創建一個簡單的表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
-- 添加一個索引
CREATE INDEX idx_username ON users(username);
-- 嘗試回滾所有更改（對 DDL 語句不起作用）
ROLLBACK;
-- 驗證儘管有 ROLLBACK，表仍被創建
SHOW TABLES;
-- 將顯示 'users' 表

不過，MySQL 8 確實提供語句級別的原子性：

-- 這將要麼創建所有用户，要麼都不創建
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1',
           'user2'@'localhost' IDENTIFIED BY 'password2';
-- 這將要麼刪除所有表，要麼都不刪除
DROP TABLE IF EXISTS table1, table2, table3;

結論

比對一下 PostgreSQL 17 和 MySQL 8 在處理 DDL 事務時的主要差異：

PostgreSQL 的事務性 DDL 保障了複雜 shcema 遷移更高級別的安全性。通過將多個相關更改包裝在事務中，管理員可以確保數據庫保持一致狀態（即使遷移過程中出現問題）。相比之下，MySQL 8 在語句級別引入原子性 DDL，意味着每個單獨的 DDL 語句都作為全有或全無的操作執行。雖然這相比早期版本的 MySQL 是一個顯著改進，但它仍然不支持多語句 DDL 事務。

參考資料

• PostgreSQL Wiki Transactional DDL in PostgreSQL: A Competitive Analysis
• MySQL Documentation Atomic Data Definition Statement Support
• MySQL Blog Atomic DDL in MySQL 8.0
• MySQL Documentation Statements That Cannot Be Rolled Back

💡 更多資訊，請關注 Bytebase 公號：Bytebase