乐趣区

关于数据库:你的-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: 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 对迁徙操作齐全回滚后,会删除执行记录,而非置为失败状态。)这个时候咱们有以下三个选项来解决 / 防止这个问题:

  1. 必须确定降级脚本进行的地位,自行回滚降级,修复故障,而后从新运行降级过程。
  2. 必须预测每一种潜在降级失败的状况,为每一种状况编写一个回滚程序,并测试每一种状况。
  3. 应用处理事务 DDL 的数据库系统。

毋庸置疑,计划 3 是最好的,所以有时候 mysql 不肯定是最好的抉择,postgreSQL 也很不错。下次应用数据库时,数据库迁徙这一方面须要做认真的思考~

退出移动版