你的 DDL 执行是事务性的吗数据库后台开发数据迁移事务

你的 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: 0Duplicates: 0Warnings: 0mysql> ALTER TABLE users ADD COLUMN gender text; Query OK, 0 rows affected (0.01 sec) Records: 0Duplicates: 0Warnings: 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 对迁移操作完全回滚后，会删除执行记录，而非置为失败状态。）这个时候我们有以下三个选项来解决/避免这个问题：