如何在SQLite中使用回滚模式？Python写事务锁隔离实现问询

核心问题说明 #

要实现NFS存储上SQLite的单写多读隔离，且写事务期间阻止读操作，核心需解决SQLite原生锁机制在NFS上的兼容性问题——NFS的文件锁实现存在延迟，原生SQLite锁可能失效，必须通过操作系统级文件锁来强制控制读写隔离。

锁定实现步骤 #

1. 绕过SQLite原生锁，用系统级文件锁控制读写 #

SQLite默认的隔离级别在NFS下不可靠，直接对数据库文件加操作系统级锁更可靠：

• 写操作前获取排他锁：阻止所有读写操作，确保单写
• 读操作前获取共享锁：允许多个读操作同时执行，但阻止写操作

2. 跨平台锁实现代码 #

写操作代码（Linux/macOS）

import sqlite3
import fcntl
import os

# NFS挂载路径下的数据库文件
db_path = "/mnt/nfs/الموارد البشرية & الرعاية الطبية/HR2.db"

# 先打开数据库文件句柄用于加锁
db_file = open(db_path, "rb+")
try:
    # 获取排他锁，阻塞直到拿到锁
    fcntl.flock(db_file, fcntl.LOCK_EX)
    
    # 执行写事务
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("INSERT INTO connected VALUES (?,?,?,?)", (record[1], dateYY, first_date, final_date))
    conn.commit()
finally:
    # 释放锁并关闭所有资源
    fcntl.flock(db_file, fcntl.LOCK_UN)
    db_file.close()
    conn.close()

读操作代码（Linux/macOS）

import sqlite3
import fcntl
import os

db_path = "/mnt/nfs/الموارد البشرية & الرعاية الطبية/HR2.db"

db_file = open(db_path, "rb")
try:
    # 获取共享锁，允许多读、阻止写
    fcntl.flock(db_file, fcntl.LOCK_SH)
    
    # 执行读操作
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM connected")
    results = cursor.fetchall()
finally:
    fcntl.flock(db_file, fcntl.LOCK_UN)
    db_file.close()
    conn.close()

Windows平台适配

将fcntl替换为msvcrt模块实现锁控制：

import msvcrt
import os

# 排他锁
msvcrt.locking(db_file.fileno(), msvcrt.LK_LOCK, os.path.getsize(db_path))
# 释放锁
msvcrt.locking(db_file.fileno(), msvcrt.LK_UNLCK, os.path.getsize(db_path))

3. 关键注意事项 #

• NFS配置要求：确保NFS挂载时启用lockd和statd服务，避免锁失效
• 超时处理：可给锁操作加LOCK_NB参数实现非阻塞，配合重试逻辑避免死等
• 连接复用：减少频繁创建关闭连接，降低锁争抢概率
• 异常处理：必须保证锁在任何异常场景下都能释放，避免死锁

原代码问题修正 #

你提供的原代码未做任何锁控制，在NFS环境下会出现：

• 多写操作同时执行导致数据损坏
• 写事务期间读操作读到脏数据
  必须添加上述系统级文件锁逻辑才能保证隔离性

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