如何创建任一查询失败即回滚全部操作的数据库事务？

问题根源 #

• DDL隐式提交：MySQL里像CREATE TABLE这类DDL操作，默认会自动触发事务隐式提交——执行完DDL，当前事务直接被提交，后面的ROLLBACK根本管不到之前的DDL操作。
• DML错误不终止事务：默认配置下，单条DML语句执行失败时，MySQL只会停掉这条错误语句，不会终止整个事务，之前成功的操作会保留下来。

对应解决方案 #

1. 处理DDL操作的事务回滚 #

如果你的MySQL版本是8.0.14及以上，直接用MySQL的原子DDL特性就行：

• 这个特性支持把InnoDB引擎的DDL操作纳入事务，只要事务里有操作失败，包括DDL本身，整个事务都会回滚。
• 不需要额外配置，直接在事务里写DDL即可，但要注意必须是InnoDB表的操作（比如CREATE TABLE时指定ENGINE=InnoDB）。

如果是8.0.14以下的旧版本：

• 别在事务里混合DDL和DML，尽量把DDL放到事务外面执行；如果非要在事务里做，旧版本的DDL还是会隐式提交，效果有限，建议升级版本更靠谱。

2. 处理DML操作的事务回滚 #

要做到“任一DML失败就全量回滚”，调整sql_mode配置就行：

• 把sql_mode设为TRADITIONAL（传统模式），这个模式下，只要单条DML出错，整个事务会直接终止并回滚所有操作。
• 临时生效（只对当前会话有效）：

SET SESSION sql_mode = 'TRADITIONAL';

• 永久生效的话，修改MySQL的配置文件（比如my.cnf或者my.ini），添加一行：

sql_mode = TRADITIONAL

改完后重启MySQL服务。

3. 完整示例（MySQL 8.0.14+） #

SET SESSION sql_mode = 'TRADITIONAL';
START TRANSACTION;

-- 用InnoDB引擎创建表，支持原子DDL
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
) ENGINE=InnoDB;

-- 这条插入会失败，因为Table_2不存在
INSERT INTO Table_2 VALUES(5); 

-- 只有所有操作都成功时，才会执行提交；只要有错误，事务自动回滚
COMMIT;

这个示例里，INSERT失败后，整个事务回滚，Persons表也不会被创建出来。

4. 应用程序端配合处理 #

如果是在代码里执行事务，最好在程序里捕获SQL执行的异常——一旦发现某个操作报错，就主动调用ROLLBACK，确保事务状态正确。比如Python用pymysql、Java用JDBC的时候，都能通过异常捕获来实现这一点。

内容的提问来源于stack exchange，提问作者Lith