sqlite3：Python 内置数据库如何执行事务

做的事:同一个文件,多个连接

实验脚本(节选) w = sqlite3.connect(path, timeout=0.2) r = sqlite3.connect(path, timeout=0.2) w.execute("insert into t(v) values ('uncommitted')") # 没有 commit r.execute("select count(*) from t") # 另一个连接读 os.listdir(dirname) # 看磁盘上多了什么

sqlite 库版本 = 3.53.4 | sqlite3 模块 autocommit 默认 = LEGACY_TRANSACTION_CONTROL

验证一:到底什么时候 BEGIN

  connect + create table + commit 之后     in_transaction = False
  执行 SELECT 之后                           in_transaction = False
  执行 INSERT 之后                           in_transaction = True
  rollback() 之后                          in_transaction = False
  rollback 后表里行数 = 0
  执行 DDL(create table) 之后                in_transaction = False

连接建立、SELECT 之后都不在事务里;第一条 INSERT 之后 in_transaction 变成 True——模块在 DML 语句前自动补了一个 BEGIN。rollback() 之后表里 0 行,刚才的插入没有留下。最后一行说明:DDL(create table)不会开启事务,它立即生效,不受之后 rollback() 影响(本机 3.14 实测)。

验证二:未提交的数据别人看不到,第二个写连接被挡在门外

  写连接自己看到行数 = 1
  读连接看到行数     = 0
  事务进行中, 目录里的文件: ['a.db', 'a.db-journal']
  第二个写连接: OperationalError: database is locked
  commit 后, 读连接看到行数 = 1 | 文件: ['a.db']

写连接自己数到 1 行,读连接是 0 行;此时目录里多出 a.db-journal——这是 SQLite 默认的回滚日志(journal_mode=delete):旧页面先备份到日志里,提交后日志删除。第二个写连接想插入,等了 0.2 秒就得到 OperationalError: database is locked。commit 之后日志文件消失,读连接看到 1 行。

不指定 timeout, 第二个写连接等了 5.21 秒后报: database is locked

验证三:with conn 管提交和回滚,不管关闭

  with 块结束后 in_transaction = False | 连接还能用: 2 行
  异常出块后行数 = 2 (回滚了)

with 块正常结束会 commit,抛异常会 rollback——上面第二次块里插入的 will roll back 确实没留下(行数仍是 2)。但连接没有被关闭:出块后还能继续查询。真的要释放文件句柄得自己 conn.close()(或用 contextlib.closing)。

验证四:一个没读完的查询,能让写方 commit 失败

  journal_mode=delete  读连接有未读完的查询时, 写方 commit 失败: database is locked; 目录: ['b.db', 'b.db-journal']
  journal_mode=wal     读连接有未读完的查询时, 写方 commit 成功; 目录: ['b.db', 'b.db-wal', 'b.db-shm']

读连接执行了 select 只取了一行,游标还开着,占着共享锁;写连接想 commit,DELETE 日志模式下要独占数据库文件,等不到就 database is locked。同样的场景切到 journal_mode=wal 就成功了,目录里出现 -wal、-shm 两个文件:写入先追加到 WAL 文件,读方仍然读旧快照,互不阻塞。所以「读的地方忘记 fetchall / 关游标」在默认模式下会以「写失败」的形式表现出来。WAL 也不是没有代价——它需要 -shm 共享内存文件,不适合放在网络文件系统上;这条我没有验证,请以官方文档为准。

验证五:commit 次数决定成本

每条 commit    synchronous=full  1000 行 用时    266.0 ms  → 每行    266.0 µs
每条 commit    synchronous=off   1000 行 用时    183.5 ms  → 每行    183.5 µs
一个事务提交       synchronous=full 10000 行 用时      7.9 ms  → 每行      0.8 µs

每条语句单独 commit,每行要 266 µs;放进一个事务提交,每行只要 0.8 µs,差了约 332 倍。把 synchronous 设成 off 只把逐条 commit 降到 184 µs,说明代价不全在 fsync,创建/删除日志文件等开销也很显著。注意这里逐条提交只跑了 1000 行、单事务跑了 10000 行,表里用的是每行摊销值,不同数据量下绝对值会变。

分步演示:两个连接在同一个文件上