为何第二个连接执行commit()时第一个连接会释放PENDING锁？

问题场景 #

在以下代码中，conn1尝试写入时因conn2持有SHARED锁失败，进而获取了PENDING锁；但当conn2执行commit()后，conn1的PENDING锁被自动释放，导致新的连接可以正常读取数据库。请解释该现象：

import sqlite3

db = "file:db?mode=memory&cache=shared"

conn = sqlite3.connect(db)
conn1 = sqlite3.connect(db)
conn2 = sqlite3.connect(db)

# Initialize
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);")
conn.commit()

conn1.execute("BEGIN")
conn2.execute("BEGIN")
conn1.execute("SELECT * FROM users")
conn2.execute("SELECT * FROM users")
# conn1/conn2 have SHARED locks now

# Will fail
try:
    conn1.execute("INSERT INTO users (name) VALUES (?)", ("conn1",))
except Exception as e:
    print('conn1: failed to insert:',e)

# conn1 now has a PENDING lock, nobody else can read
try:
    conn.execute("SELECT * FROM users")
except Exception as e:
    print('conn: failed to select:',e)

# conn2 still has its shared lock, so it can read
conn2.execute("SELECT * FROM users")

# Strangely, if conn2 calls commit(), conn1 drops its PENDING Lock
conn2.commit()

# Why did conn1 drop its PENDING lock? Note: nothing was written
print(conn.execute("SELECT * FROM users").fetchall())

原因解析 #

这个现象是SQLite锁机制与Python sqlite3模块事务异常处理逻辑共同作用的结果：

1. PENDING锁的本质作用
   SQLite锁采用层级结构：SHARED（读锁）→ PENDING → RESERVED → EXCLUSIVE（写锁）。当conn1尝试写入时，需要将SHARED锁升级为EXCLUSIVE锁，但此时conn2仍持有SHARED锁，无法直接升级。SQLite会给conn1分配PENDING锁——它的作用是阻止新连接获取SHARED锁，但已持有SHARED锁的连接（如conn2）仍可继续读取。

2. INSERT失败导致事务失效
   当conn1的INSERT操作因锁冲突失败并抛出异常时，sqlite3模块会将当前事务标记为回滚状态。此时该事务已无法执行任何后续有效操作，相当于进入“废弃”状态。

3. conn2提交触发锁回收
   conn2执行commit()后会释放持有的SHARED锁，此时数据库已无其他SHARED锁。但由于conn1的事务早已因INSERT失败失效，SQLite会自动回收该事务持有的PENDING锁——这个锁已无存在意义，对应的事务无法再发起任何写入尝试。

4. 最终效果
   PENDING锁释放后，新连接（如conn）可以正常获取SHARED锁，执行SELECT操作。

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