mysql 大事务 你的 DDL 执行是事务性的吗

最近在项目中集成 flyway，在官方文档中有关事务处理方面，发现了一段比较有意思的一段话：

If your database cleanly supports DDL statements within a transaction, failed migrations will always be rolled back (unless they were marked as non-transactional).

If on the other hand your database does NOT cleanly supports DDL statements within a transaction (by for example issuing an implicit commit before and after every DDL statement), Flyway won’t be able to perform a clean rollback in case of failure and will instead mark the migration as failed, indicating that some manual cleanup may be required.

上面的大概意思是flyway 在做迁移时，如果数据库支持事务中的 DDL ，若发生错误则会很干净的回滚；相反如果不支持，flyway则不会干净的回滚，同时将迁移记录表（默认是flyway_schema_history）中的记录设置为失败。

其中还有一句：by for example issuing an implicit commit before and after every DDL statement，也就是说有的数据库执行 DDL 时会存在隐式提交。那这是怎么一回事呢，我们就来一起研究一下。

DDL

首先什么是DDL？ DDL = Data Define Language，用于定义数据结构，我们平时所说的DDL基本上都是 SQL DDL，例如 CREATE TABEL T (column int)语句。当然不仅仅是建表语句，涉及对表名的修改、列的增加删除、更改列的类型等操作都算作DDL。当我们执行 DDL 时将其包裹在事务中也是有必要的，虽然语法和数据上不容易出错，但是如果遇到如“数据库磁盘满导致DDL失败”，仍可以做 rollback 操作来进行恢复。

事务性

我们都知道在数据库中的事务会遵循 ACID 原则，即原子性（Atomic）一致性（consistent）隔离性 （isolated）和持久性（durable），在一个事务中的 sql 语句要么被全部执行，要么都没有执行。在本文中，我们把多条DDL语句放在一个事务中执行，例如一条建表语句、一条新增列的语句，我们也希望这些 DDL 能够全部提交或者全部回滚。我们举一个例子（以 PostgreSQL 为例），首先创建一张表并插入数据：

create table users ( name varchar(256) not null );insert into users values ("Alice 25 female");

下面我们打算新增两列：年龄（age）和性别（gender），并把原数据进行处理

alter table users add column age integer not null;alter table users add column gender varchar(10) not null;update users set name=split_part(name,' ',1), age=split_part(name, ' ',2)::int, gender=split_part(name, ' ',3);

处理结果如下，正是我们想要的：

# SELECT * FROM users; name  │ age │ gender───────┼─────┼──────── Alice │  25 │ female(1 row)

事务回退？

下面就是重点了，如果在新增两列之前，原表数据如下：

SELECT * FROM users;

name

───────────────────── Alice 25 female Boberror (2 row)

随后在一个事务中执行语句

# alter table users add column age integer not null;ALTER TABLE# alter table users add column gender varchar(10) not null;ALTER TABLE# update users set name=split_part(name,' ',1), age=split_part(name, ' ',2)::int, gender=split_part(name, ' ',3);ERROR: invalid input syntax for integer: ""

啊，由于数据错误，处理失败，事务回滚，我们把错误数据处理之后再重新执行以上语句。

处理完之后，当我们重新执行上述语句时，有可能会得到这个错误：

# alter table users add column age integer not null;ERROR:  column "age" of relation "users" already exists

WTF？这是为什么呢？当我们第一次运行升级过程时，我们并没有在事务中运行它。每个 DDL 语句都在执行后立即提交。因此，我们数据库的当前状态是半迁移的：我们的表结构更新了，但没有迁移数据。

数据库欺骗了我们！

某些数据库系统（例如 MySQL）不支持在事务中运行 DDL，因此别无选择，只能将三个操作（ALTER、ALTER 和然后 UPDATE）作为三个不同的操作运行：如果其中任何一个失败，则无法恢复并回到初始状态。

我们来看看 mysql

mysql> CREATE TABLE users (name text NOT NULL);Query OK, 0 rows affected (0.03 sec)mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> ALTER TABLE users ADD COLUMN age integer;Query OK, 0 rows affected (0.05 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> ALTER TABLE users ADD COLUMN gender text;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> ROLLBACK;Query OK, 0 rows affected (0.00 sec)mysql> DESC ingredients;+----------+---------+------+-----+---------+-------+| Field    | Type    | Null | Key | Default | Extra |+----------+---------+------+-----+---------+-------+| name     | text    | NO   |     | NULL    |       || quantity | int(11) | YES  |     | NULL    |       || unit     | text    | YES  |     | NULL    |       |+----------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)

在上面的输出中，可以看到我们在一个事务中发出了两个 DDL 语句，然后我们回滚了该事务。 MySQL 在任何时候都没有输出任何错误，让我们认为它没有改变我们的表。然而，当检查数据库的模式时，我们可以看到没有任何东西被回滚。 MySQL 不仅不支持事务性 DDL，而且它也没有明确表明自己没有 rollback！

相反在 sqlserver 中，就可以很完美的将表结构回退。

这个特性很值得注意

事务性 DDL 是一个经常被软件工程师忽略的特性，但它是管理数据库生命周期的关键特性。

这也是在 Flyway 中如果带有 DDL 的迁移脚本执行失败后，如果数据库不支持 事务性的DDL 则只是将执行记录置为失败。（Flyway 对迁移操作完全回滚后，会删除执行记录，而非置为失败状态。）这个时候我们有以下三个选项来解决/避免这个问题：

必须确定升级脚本停止的位置，自行回滚升级，修复故障，然后重新运行升级过程。必须预测每一种潜在升级失败的情况，为每一种情况编写一个回滚程序，并测试每一种情况。使用处理事务 DDL 的数据库系统。

毋庸置疑，方案 3 是最好的，所以有时候 mysql 不一定是最好的选择，postgreSQL 也很不错。下次使用数据库时，数据库迁移这一方面需要做仔细的考虑~