如何让MariaDB在事务内语句失败时回滚整个事务？

问题说明 #

多数数据库管理系统（DBMS）中，事务内某条语句执行失败会触发整个事务回滚，但MariaDB/MySQL默认仅回滚失败的语句。例如执行以下SQL后，事务中成功插入的banana记录会保留：

DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code INT CHECK(code>0),
    data VARCHAR(255)
) ENGINE=INNODB;

INSERT INTO test(code,data) VALUES(23,'apple');

START TRANSACTION;

INSERT INTO test(code,data) VALUES(45,'banana');
INSERT INTO test(code,data) VALUES(-3,'banjo');     -- 违反CHECK约束执行失败
COMMIT;

SELECT * FROM test;

解决方案 #

要让MariaDB在事务出错时回滚整个事务，可通过以下方式实现：

1. 临时开启会话级严格模式 #

在事务执行前运行以下命令，设置会话的sql_mode为传统模式：

SET SESSION sql_mode = 'STRICT_ALL_TABLES,TRADITIONAL';

TRADITIONAL模式会让MariaDB在遇到语句执行错误时立即终止事务，并回滚所有已完成的操作，符合标准事务的原子性要求。

2. 手动捕获错误并触发回滚 #

在SQL脚本或存储过程中，通过错误处理机制主动触发回滚。示例存储过程如下：

DELIMITER //
CREATE PROCEDURE insert_test_records()
BEGIN
    -- 捕获所有SQL异常，触发回滚
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '事务执行失败，已回滚所有操作';
    END;

    START TRANSACTION;
    INSERT INTO test(code,data) VALUES(45,'banana');
    INSERT INTO test(code,data) VALUES(-3,'banjo');
    COMMIT;
END //
DELIMITER ;

调用该存储过程时，只要任意一条语句执行失败，就会自动回滚整个事务。

3. 全局配置永久生效 #

若希望所有会话都遵循该规则，可修改MariaDB配置文件（如my.cnf/my.ini），在[mysqld]段落添加：

sql_mode = STRICT_ALL_TABLES,TRADITIONAL

保存配置后重启MariaDB服务，该设置将永久生效。

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