你的 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).上面的大概意思是flyway 在做迁移时,如果数据库支持事务中的 DDL ,若发生错误则会很干净的回滚;相反如果不支持,flyway则不会干净的回滚,同时将迁移记录表(默认是
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_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 对迁移操作完全回滚后,会删除执行记录,而非置为失败状态。)这个时候我们有以下三个选项来解决/避免这个问题:
- 必须确定升级脚本停止的位置,自行回滚升级,修复故障,然后重新运行升级过程。
- 必须预测每一种潜在升级失败的情况,为每一种情况编写一个回滚程序,并测试每一种情况。
- 使用处理事务 DDL 的数据库系统。
推荐阅读
- SAP|SAP UI5 setProperty 的执行逻辑单步调式和分析
- 注意!你的|注意!你的 Navicat 可能被下毒了...
- pytest(13)-多线程、多进程执行用例
- 好用不卡,这些插件和配置让你的|好用不卡,这些插件和配置让你的 Webstorm 更牛逼!
- 凌晨三点的程序员,别让你的辛苦被辜负
- PaddleGAN RealSR 超分辨率重建
- Entity|Entity Framework Core中执行SQL语句和存储过程的方法介绍
- SpringMVC执行步骤、Model的使用详解
- 群晖|个人网络笔记本TiddlyWiki
- 你的取舍很重要