数据库原理与选型 / 系统课时
处理锁冲突与异常事务
处理锁冲突与异常事务
本节用两个 SQLite 连接复现 database is locked,再把长事务、等待超时、有限重试和异常回滚组合成一套可执行处理方式。完成后,你能区分正常写竞争与事务泄漏,并验证失败不会留下半完成数据。
学习目标
- 复现并解释 SQLite 单写者导致的锁冲突
- 使用超时与有限重试处理短暂写竞争
- 用显式事务和回滚保证多步写入要么全部成功、要么全部撤销
先复现锁冲突
SQLite 可以有多个连接,但同一时刻只有一个写事务真正推进。下面脚本让连接 A 用 BEGIN IMMEDIATE 提前取得写事务,连接 B 在零等待模式下立即尝试写入,因此会稳定触发锁错误。
import sqlite3
from pathlib import Path
db_path = Path("locks-demo.db")
if db_path.exists():
db_path.unlink()
conn_a = sqlite3.connect(db_path, timeout=0)
conn_b = sqlite3.connect(db_path, timeout=0)
for conn in (conn_a, conn_b):
conn.execute("PRAGMA journal_mode = WAL")
conn.execute(
"CREATE TABLE IF NOT EXISTS jobs ("
"id INTEGER PRIMARY KEY, "
"name TEXT NOT NULL"
")"
)
conn.commit()
conn_a.execute("BEGIN IMMEDIATE")
conn_a.execute("INSERT INTO jobs(name) VALUES (?)", ("writer-a",))
try:
conn_b.execute("INSERT INTO jobs(name) VALUES (?)", ("writer-b",))
conn_b.commit()
except sqlite3.OperationalError as exc:
print(exc)
finally:
conn_a.rollback()
conn_a.close()
conn_b.close()预期输出
database is lockedWAL 可以改善读写并发,但不能把 SQLite 变成多写者数据库;两个写事务竞争时,后来的写者仍然需要等待前一个写者结束。

把长事务缩到必要范围
最有效的修复通常不是“把超时调大”,而是缩短持锁时间。网络请求、用户输入、文件读取、复杂计算都应尽量放到事务外;进入事务后只做必须保持原子性的数据库操作,然后尽快提交或回滚。
BEGIN IMMEDIATE 适合明确知道接下来要写的路径,因为它会在事务开始时争取写锁,让锁冲突更早暴露。普通延迟事务则可能先读后写,直到第一次写入才发现竞争。
加入超时、有限重试和回滚
短暂锁竞争可以等待和重试,但重试必须有限,并且只针对可恢复的锁错误。下面的完整示例同时演示事务回滚:第二条插入故意违反唯一约束,最终两条写入都不会保留。
import sqlite3
import time
DB = "app.db"
def open_db():
conn = sqlite3.connect(DB, timeout=2)
conn.execute("PRAGMA foreign_keys = ON")
conn.execute("PRAGMA journal_mode = WAL")
return conn
def insert_pair(first_name, second_name):
delays = [0.1, 0.2, 0.4]
for attempt in range(len(delays) + 1):
conn = open_db()
try:
conn.execute("BEGIN IMMEDIATE")
conn.execute("INSERT INTO jobs(name) VALUES (?)", (first_name,))
conn.execute("INSERT INTO jobs(name) VALUES (?)", (second_name,))
conn.commit()
return
except sqlite3.OperationalError as exc:
conn.rollback()
if "locked" not in str(exc).lower() or attempt == len(delays):
raise
time.sleep(delays[attempt])
except Exception:
conn.rollback()
raise
finally:
conn.close()
with open_db() as conn:
conn.execute("DROP TABLE IF EXISTS jobs")
conn.execute(
"CREATE TABLE jobs ("
"id INTEGER PRIMARY KEY, "
"name TEXT NOT NULL UNIQUE"
")"
)
conn.commit()
try:
insert_pair("sync-task", "sync-task")
except sqlite3.IntegrityError as exc:
print(type(exc).__name__)
with open_db() as conn:
count = conn.execute("SELECT COUNT(*) FROM jobs").fetchone()[0]
print(f"rows={count}")预期输出
IntegrityError
rows=0这里的 rows=0 是验收重点:第一条插入已经执行,但第二条失败后显式回滚,整个事务没有留下部分结果。锁重试与业务异常要分开处理,唯一约束、语法错误等确定性失败不应该被反复重试。
按证据判断根因
遇到 database is locked 时,优先确认是否存在长时间未提交的写事务,再检查是否把慢操作放进事务、多个进程是否同时高频写入,以及连接是否遗漏 commit()、rollback() 或 close()。单纯把 timeout 从 2 秒改成 30 秒,只会让问题更晚暴露。
修复后要同时验证两件事:正常竞争能在可接受时间内完成,真正失败时数据库仍保持一致。只有“错误消失”而没有事务一致性验证,不能证明问题已经解决。