如何在SQLite中实现多语句事务的完全回滚？

测试环境 #

• macOS系统
• SQLite 3.43.2版本
• 空内存数据库

测试1：表定义带冲突处理的事务执行 #

执行如下SQL：

.mode markdown
.headers on

create table job(
    name text not null,
    billable real not null,
    check(billable > 0.0) on conflict abort
);

begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
insert into job values ("clean", 0.5);
commit;

select * from job;

预期结果：因第二条INSERT违反CHECK约束，整个事务回滚，最终查询无数据。
实际结果：

Runtime error near line 12: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |

调整表定义的冲突处理规则（移除on conflict abort或替换为on conflict rollback），行为无变化。

测试2：INSERT语句指定ROLLBACK的事务执行 #

在新的空内存数据库执行：

create table job(
    name text not null,
    billable real not null,
    check(billable > 0.0)
);

begin transaction;
insert or rollback into job values ("calibrate", 1.5);
insert or rollback into job values ("reset", -0.5);
insert or rollback into job values ("clean", 0.5);
commit;

select * from job;

实际结果：

Runtime error near line 12: CHECK constraint failed: billable > 0.0 (19)
Runtime error near line 14: cannot commit - no transaction is active
| name  | billable |
|-------|----------|
| clean | 0.5      |

第二条INSERT触发错误后，命令行仍执行了第三条INSERT，且clean被成功插入（此时事务已回滚，第三条INSERT处于自动提交模式）。

测试3：无显式事务的INSERT执行 #

在新数据库执行：

insert or rollback into job values ("calibrate", 1.5);
insert or rollback into job values ("reset", -0.5);
insert or rollback into job values ("clean", 0.5);

实际结果：

Runtime error near line 11: CHECK constraint failed: billable > 0.0 (19)
|   name    | billable |
|-----------|----------|
| calibrate | 1.5      |
| clean     | 0.5      |

此结果符合SQLite默认行为，但不符合“全事务回滚”的需求。

核心问题 #

是否可以在SQLite中创建真正的多语句事务，实现事务内任意语句失败则整个事务回滚，类似如下（无效语法）的期望效果？

-- 无效SQLite语法
begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
insert into job values ("clean", 0.5);
commit or rollback;

解决方案 #

SQLite内核本身支持原子事务，之前的问题是命令行shell默认在出错后继续执行后续语句导致的。可以通过以下两种方式实现需求：

1. 命令行环境下：开启错误终止模式 #

在SQLite命令行中，使用.exit on error命令，开启后一旦有语句执行失败，shell会立即终止，未提交的事务会自动回滚，不会执行后续语句。

示例代码：

.mode markdown
.headers on

.exit on error

create table job(
    name text not null,
    billable real not null,
    check(billable > 0.0)
);

begin transaction;
insert into job values ("calibrate", 1.5);
insert into job values ("reset", -0.5);
insert into job values ("clean", 0.5);
commit;

select * from job;

执行后，第二条INSERT触发错误，shell立即终止，事务自动回滚，最终查询无数据，符合预期。

2. 应用程序环境下：通过API控制事务逻辑 #

如果是在代码中使用SQLite，只需捕获SQL执行错误，主动回滚事务并停止后续语句执行即可。以Python为例：

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

try:
    # 创建表
    cursor.execute('''
        create table job(
            name text not null,
            billable real not null,
            check(billable > 0.0)
        )
    ''')
    # 开启事务
    conn.execute('begin transaction')
    # 执行插入语句
    cursor.execute('insert into job values ("calibrate", 1.5)')
    cursor.execute('insert into job values ("reset", -0.5)')
    cursor.execute('insert into job values ("clean", 0.5)')
    # 提交事务
    conn.commit()
except sqlite3.Error as e:
    # 出错则回滚事务
    conn.rollback()
    print(f"执行错误: {e}")

# 查询最终结果
cursor.execute('select * from job')
print("最终表数据:", cursor.fetchall())

conn.close()

这段代码中，第二条INSERT出错后进入异常块，执行全事务回滚，最终表中无数据。

补充说明 #

• INSERT OR ROLLBACK的作用是：当当前INSERT语句冲突时，回滚整个事务，但命令行shell仍会继续执行后续语句，这就是测试2中clean被插入的原因——第三条INSERT是在事务回滚后的自动提交模式下执行的，不属于原事务。
• SQLite事务的原子性是内核级特性：只要事务未提交，且在出错后执行回滚，就能保证所有操作被撤销。

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