面试题：SQLite死锁产生的场景及检测方法

可能导致死锁的场景

事务交叉访问：假设有两个事务 T1 和 T2。T1 首先获取表 A 的锁，然后尝试获取表 B 的锁；而 T2 首先获取表 B 的锁，接着尝试获取表 A 的锁。如果 T1 在获取表 A 锁后，T2 在获取表 B 锁后，它们同时去获取对方已持有的锁，就会发生死锁。例如：

-- T1事务
BEGIN;
UPDATE TableA SET column1 = 'value1' WHERE condition; -- 获取TableA锁
UPDATE TableB SET column2 = 'value2' WHERE condition; -- 等待获取TableB锁

-- T2事务
BEGIN;
UPDATE TableB SET column2 = 'value3' WHERE condition; -- 获取TableB锁
UPDATE TableA SET column1 = 'value4' WHERE condition; -- 等待获取TableA锁

多层嵌套事务锁竞争：在复杂的业务逻辑中，存在多层嵌套事务，不同事务嵌套结构中对相同资源的获取顺序不一致。例如，事务 T1 按顺序获取资源 R1、R2、R3，而事务 T2 按顺序获取资源 R3、R1、R2。如果 T1 获取了 R1，T2 获取了 R3，然后 T1 尝试获取 R2，T2 尝试获取 R1，就会形成死锁。

SQLite检测死锁机制

SQLite使用一种叫做“死锁检测算法”的机制来检测死锁。当一个事务尝试获取一个锁，而这个锁已经被另一个事务持有，并且这个持有锁的事务正在等待其他锁时，SQLite会检查是否存在循环等待的情况（即死锁）。如果检测到死锁，SQLite会选择一个事务（通常是开销最小的事务）作为牺牲品，回滚该事务，并向应用程序返回一个 SQLITE_BUSY 错误代码，表明发生了死锁。

代码层面优化避免死锁

按照固定顺序获取锁：在应用程序代码中，确保所有事务按照相同的顺序获取锁。例如，总是先获取表 A 的锁，再获取表 B 的锁，这样可以避免交叉访问导致的死锁。
减少锁的持有时间：尽量缩短事务的执行时间，减少锁的持有时间。在事务中只执行必要的操作，避免在事务内进行长时间的计算或等待外部资源。例如，将一些非数据库操作移到事务之外执行。
使用较小粒度的锁：如果可能，使用行级锁而不是表级锁。行级锁可以减少锁的竞争范围，降低死锁的可能性。在SQLite中，默认使用的是表级锁，但某些操作（如 UPDATE 带有 WHERE 条件定位到具体行）会尽量使用更细粒度的锁。
重试机制：当应用程序接收到 SQLITE_BUSY 错误（可能是死锁导致）时，可以实现一个重试机制。在捕获到该错误后，等待一段随机时间（避免同时重试再次冲突），然后重新执行事务。例如在Python中：

import sqlite3
import time
import random

def execute_with_retry(sql, retries=3):
    for attempt in range(retries):
        try:
            conn = sqlite3.connect('example.db')
            cursor = conn.cursor()
            cursor.execute(sql)
            conn.commit()
            conn.close()
            return
        except sqlite3.OperationalError as e:
            if 'SQLITE_BUSY' in str(e):
                wait_time = random.uniform(0.1, 1)
                time.sleep(wait_time)
            else:
                raise
    raise Exception("Max retry attempts reached")

面试题：SQLite死锁产生的场景及检测方法

知识考点

面试题答案

可能导致死锁的场景

SQLite检测死锁机制

代码层面优化避免死锁