数据库原理与选型 / 系统课时

处理锁冲突与异常事务

处理锁冲突与异常事务

本节用两个 SQLite 连接复现 database is locked,再把长事务、等待超时、有限重试和异常回滚组合成一套可执行处理方式。完成后,你能区分正常写竞争与事务泄漏,并验证失败不会留下半完成数据。

学习目标

  • 复现并解释 SQLite 单写者导致的锁冲突
  • 使用超时与有限重试处理短暂写竞争
  • 用显式事务和回滚保证多步写入要么全部成功、要么全部撤销

先复现锁冲突

SQLite 可以有多个连接,但同一时刻只有一个写事务真正推进。下面脚本让连接 A 用 BEGIN IMMEDIATE 提前取得写事务,连接 B 在零等待模式下立即尝试写入,因此会稳定触发锁错误。

python
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()

预期输出

text
database is locked

WAL 可以改善读写并发,但不能把 SQLite 变成多写者数据库;两个写事务竞争时,后来的写者仍然需要等待前一个写者结束。

两个连接发生 SQLite 写锁竞争的时序

把长事务缩到必要范围

最有效的修复通常不是“把超时调大”,而是缩短持锁时间。网络请求、用户输入、文件读取、复杂计算都应尽量放到事务外;进入事务后只做必须保持原子性的数据库操作,然后尽快提交或回滚。

BEGIN IMMEDIATE 适合明确知道接下来要写的路径,因为它会在事务开始时争取写锁,让锁冲突更早暴露。普通延迟事务则可能先读后写,直到第一次写入才发现竞争。

加入超时、有限重试和回滚

短暂锁竞争可以等待和重试,但重试必须有限,并且只针对可恢复的锁错误。下面的完整示例同时演示事务回滚:第二条插入故意违反唯一约束,最终两条写入都不会保留。

python
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}")

预期输出

text
IntegrityError
rows=0

这里的 rows=0 是验收重点:第一条插入已经执行,但第二条失败后显式回滚,整个事务没有留下部分结果。锁重试与业务异常要分开处理,唯一约束、语法错误等确定性失败不应该被反复重试。

按证据判断根因

遇到 database is locked 时,优先确认是否存在长时间未提交的写事务,再检查是否把慢操作放进事务、多个进程是否同时高频写入,以及连接是否遗漏 commit()、rollback() 或 close()。单纯把 timeout 从 2 秒改成 30 秒,只会让问题更晚暴露。

修复后要同时验证两件事:正常竞争能在可接受时间内完成,真正失败时数据库仍保持一致。只有“错误消失”而没有事务一致性验证,不能证明问题已经解决。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

处理锁冲突与异常事务

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通