Python MySQL脚本插入数据未等待表锁却返回执行成功的问题

问题场景 #

使用Python 3.9对接MySQL 8，执行以下插入代码：

my_db_cursor = dbi.my_conn.cursor()
my_yr_sql = (
    'INSERT INTO cal_years '
    '(calendar_year, start_dt, end_dt) '
    'VALUES '
    '(%s, %s, %s) '
)
yr_info = (y, start_dt, end_dt)
my_db_cursor.execute(my_yr_sql, yr_info)

dbi.my_conn.commit()
my_db_cursor.close()
dbi.close_dbs()

（变量y为整数，start_dt、end_dt为datetime类型）

目标表被另一进程锁定时，脚本执行无报错（已包含try-except块），但插入的数据不可见；直到另一进程释放锁（回滚或提交）后，数据才变为可见。原本预期会触发Lock wait timeout exceeded错误，但实际直接返回成功。

原因分析 #

1. InnoDB锁机制的等待特性：InnoDB默认使用行级锁，但如果另一进程持有表级锁（如执行LOCK TABLES cal_years WRITE），或因间隙锁/Next-Key Lock导致插入请求被阻塞，你的插入操作会进入后台等待队列，而非立刻抛出异常。
2. 锁等待超时参数默认值较高：MySQL默认的innodb_lock_wait_timeout为50秒，只要另一进程在超时前释放锁，你的事务就会自动完成提交，不会触发异常。
3. 事务隔离级别的影响：InnoDB默认隔离级别为REPEATABLE READ，在锁未释放、事务未完成提交时，当前会话无法查询到未提交的插入数据，造成“插入成功但不可见”的假象。

解决方法 #

1. 缩短锁等待超时时间 #

通过会话级参数调整锁等待超时，一旦等待超过设定时间就抛出异常，方便捕获处理：

# 在创建cursor后执行
my_db_cursor.execute("SET SESSION innodb_lock_wait_timeout = 10;")  # 设置为10秒

2. 使用非阻塞插入语法（MySQL 8.0.1+） #

针对行级锁场景，使用NOWAIT关键字，无法获取锁时立刻抛出异常：

my_yr_sql = (
    'INSERT INTO cal_years '
    '(calendar_year, start_dt, end_dt) '
    'VALUES '
    '(%s, %s, %s) NOWAIT '
)

注：该语法不适用于表级锁场景。

3. 优化锁持有逻辑 #

检查持有锁的进程，尽量缩短其事务执行时间，避免长时间占用锁；若使用表级锁，评估是否可改为行级锁减少冲突。

4. 显式检查锁状态（慎用） #

通过查询进程列表判断是否存在锁等待，注意存在竞态问题（查询后锁状态可能变化）：

my_db_cursor.execute("SHOW PROCESSLIST;")
for proc in my_db_cursor.fetchall():
    if proc[2] == 'Locked' and proc[3] == 'cal_years':
        raise RuntimeError("目标表已被锁定，无法执行插入")

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