使用pyodbc操作Microsoft Access数据库时遇记录锁定错误求助

问题背景 #

执行INSERT/UPDATE操作时触发锁定错误：

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] Could not update; currently locked by user 'admin' on machine 'SAEED'. (-1102) (SQLExecDirectW)")

操作场景：通过Python的pyodbc库执行SQL语句修改Access数据库记录。

需求：

• 无错误执行INSERT/UPDATE操作
• 规避记录锁定问题
• 实现优雅的并发处理（等待锁释放或冲突避免）

用户代码片段：

cursor.execute(create_table_query)
conn.commit()

# Insert data into the table
insert_query = f'''
INSERT INTO {table_name} (Province, County, District, City, Region, Neighborhood, Title, Address, Type, FClass, Latitude, Longitude)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
'''
cursor.execute(insert_query, (
    data['province'],
    data['county'],
    data['district'],
    data['city'],
    data['region'],
    data['neighborhood'],
    data['title'],
    data['address'],
    data['type'],
    data['fclass'],
    data['geom']['coordinates'][1],  # Latitude
    data['geom']['coordinates'][0]   # Longitude
))
conn.commit()

解决办法 #

1. 排查外部锁定源 #

• 检查SAEED机器上是否打开了该Access数据库（包括最小化或隐藏的窗口），全部关闭
• 确认是否有其他程序/脚本正在连接数据库，终止未正常释放连接的进程

2. 调整连接字符串参数 #

Access ODBC驱动支持锁定模式配置，修改连接字符串来优化并发行为：

conn_str = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=C:\your\db\path.accdb;'
    r'LOCKING MODE=3;'  # 乐观并发：仅提交时检查记录是否被修改，适合低冲突场景
    r'IMPLICIT COMMITS=YES;'  # 自动提交单条语句，缩短锁持有时间
)
conn = pyodbc.connect(conn_str)

• LOCKING MODE=1：只读共享（仅查询用）
• LOCKING MODE=2：悲观锁（操作时立即锁定记录，适合高冲突场景）

3. 缩短锁持有时间 #

• 尽量压缩事务执行时长，避免长时间保持连接
• 操作完成后立即关闭游标和连接，用try/finally确保资源释放：

try:
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    # 执行create_table或insert操作
    cursor.execute(create_table_query)
    conn.commit()
    
    cursor.execute(insert_query, params)
    conn.commit()
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

4. 实现重试机制 #

捕获锁定错误，设置重试次数和间隔，等待锁释放后重试：

import time

max_retries = 5
retry_delay = 2  # 每次重试间隔2秒

for attempt in range(max_retries):
    try:
        conn = pyodbc.connect(conn_str)
        cursor = conn.cursor()
        cursor.execute(insert_query, (
            data['province'],
            data['county'],
            data['district'],
            data['city'],
            data['region'],
            data['neighborhood'],
            data['title'],
            data['address'],
            data['type'],
            data['fclass'],
            data['geom']['coordinates'][1],
            data['geom']['coordinates'][0]
        ))
        conn.commit()
        print("数据插入成功")
        break
    except pyodbc.Error as e:
        error_msg = str(e)
        if "-1102" in error_msg:
            if attempt < max_retries - 1:
                print(f"记录被锁定，{retry_delay}秒后重试（第{attempt+1}/{max_retries}次）")
                time.sleep(retry_delay)
            else:
                print("重试次数耗尽，仍无法获取锁")
                raise e
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

5. 数据库维护 #

• 打开Access数据库时，不要选择“独占打开”模式
• 定期执行“压缩和修复数据库”（Access菜单：数据库工具 > 压缩和修复数据库），解决因异常关闭导致的锁定残留问题

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