MySQL记录锁问题求助：大表特定记录无法更新

1. 定位并终止持有锁的事务 #

SHOW PROCESSLIST未发现异常时，直接查询InnoDB事务锁定信息：

SELECT trx_id, trx_state, trx_mysql_thread_id, trx_query 
FROM INFORMATION_SCHEMA.INNODB_TRX 
WHERE trx_state != 'NOT STARTED';

找到对应users表主键锁的活跃事务，用线程ID终止：

KILL [TRX_MYSQL_THREAD_ID];

2. 清理遗留的已删除列值与无效数据 #

这类问题通常和InnoDB的MVCC旧版本记录未清理有关，可通过以下操作解决：

• 临时提升Purge线程数加速清理：

  SET GLOBAL innodb_purge_threads = 4;
  

• 低峰期重建表（彻底清理碎片与无效记录）：

  ALTER TABLE xyz.users ENGINE=InnoDB;
  

  该操作会重建表结构，自动清理未被Purge的旧版本数据，同时修复表的一致性问题。

3. 应急与优化更新操作 #

• 若需临时调整锁等待时长（非根治方案）：

  SET GLOBAL innodb_lock_wait_timeout = 30;
  

• 确认更新语句的正确性：你的语句update users set level = -2 where userid = 5747443已使用主键精确匹配，无需修改，仅需确保userid无隐式类型转换即可。

4. 长期预防措施 #

• 开启慢查询日志，监控并终止长时间未提交的事务，避免锁资源被长期占用。
• 定期在低峰期优化大表：

  OPTIMIZE TABLE xyz.users;
  

• 考虑对大表进行分区拆分，降低单表内的锁冲突概率。

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