关于数据库:你的-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 = 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)

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

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: 0

mysql> ALTER TABLE users ADD COLUMN gender text;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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 也很不错。下次应用数据库时，数据库迁徙这一方面须要做认真的思考~

关于数据库:你的-DDL-执行是事务性的吗

你的 DDL 执行是事务性的吗

DDL

事务性

事务回退？

SELECT * FROM users;

数据库坑骗了咱们！

这个个性很值得注意