Postgres 与 MySQL 执行 DDL 事务的对比
原文地址：https://www.bytebase.com/blog/postgres-vs-mysql-ddl-transaction
数据库 schema 变更作为关键操作，需要慎重规划执行；因此，执行变更是否安全可靠，是选择数据库管理系统的关键考虑因素。接下来，我们将比较 PostgreSQL 17 和 MySQL 8 对于 DDL（Data Definition Language）事务的处理，且重点关注二者的原子性和回滚能力。
什么是 DDL 事务
深入比较之前，让我们先明确 DDL 事务的含义。DDL 语句可以组合在一起，作为一个单元提交，或者在出现问题时完全回滚。
有两个重要的概念需要区分：
- 事务性 DDL：能够在多语句事务块中包含 DDL 语句，并可以选择一起提交或回滚所有语句。
- 原子性 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
