MySQL 1205错误：Lock wait timeout exceeded问题处理

MySQL 1205错误：Lock wait timeout exceeded问题处理

MySQL错误1205（Lock wait timeout exceeded; try restarting transaction）是MySQL数据库中常见的死锁和锁等待超时错误，通常在高并发场景下出现。

产生锁的主要原因：

1. 长事务阻塞：某个事务长时间未提交，持有锁资源不放，导致其他事务等待
2. 锁竞争激烈：多个事务同时竞争同一行或表的锁，形成资源争抢
3. 锁等待超时时间设置过短：默认50秒可能不足以完成某些业务操作
4. 缺乏索引或索引失效：导致锁升级为表锁，增加冲突概率

针对MySQL 8.0.16版本，记录详细分析原因，处理过程。

一、立即处理措施

1. 查找并终止阻塞进程

-- 查看当前锁等待情况
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 查看长时间运行的事务
SELECT * FROM information_schema.INNODB_TRX 
ORDER BY TRX_STARTED ASC;

-- 终止特定事务（替换trx_mysql_thread_id为实际ID）
KILL [进程ID];

临时处理，保证程序能继续运行，但是并未从根本上解决问题。

2. 调整锁等待超时时间（临时方案）

-- 将锁等待超时时间临时调整为120秒
SET GLOBAL innodb_lock_wait_timeout = 120;

临时处理，对于消耗资源大的查询120秒的等待时长，有时候也不够用。

二、解决锁的优化解决方向

1. 事务优化

• 缩短事务执行时间，避免在事务中进行复杂计算或远程调用
• 按固定顺序访问表和行，避免交叉访问导致的死锁
• 将大事务拆分为小事务，减少锁持有时间
• 合理设置隔离级别，非必要不使用Serializable

2. 数据库设计优化

• 为高频查询字段添加合适索引，避免全表扫描
• 避免热点数据问题，可以考虑数据分片
• 确保字符集一致性，避免隐式转换导致索引失效

3. 监控与预警

设置定期监控脚本，检测长事务和锁等待：

# 监控长事务脚本
SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, 
       p.TIME, p.STATE, p.INFO, 
       t.TRX_STARTED, TIMEDIFF(NOW(), t.TRX_STARTED) AS DURATION
FROM information_schema.INNODB_TRX t
JOIN information_schema.PROCESSLIST p ON t.TRX_MYSQL_THREAD_ID = p.ID
ORDER BY t.TRX_STARTED ASC;

-- 查找阻塞时间超过30秒的事务
SELECT p.ID, p.USER, p.HOST, p.DB, p.COMMAND, 
       p.TIME, p.STATE, p.INFO, 
       t.TRX_STARTED, TIMEDIFF(NOW(), t.TRX_STARTED) AS DURATION
FROM information_schema.INNODB_TRX t
JOIN information_schema.PROCESSLIST p ON t.TRX_MYSQL_THREAD_ID = p.ID
WHERE TIMESTAMPDIFF(SECOND, t.TRX_STARTED, NOW()) > 30
ORDER BY t.TRX_STARTED ASC;

三、处理过程

1. 锁等待监控脚本

-- 锁等待监控查询

SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_time_sec,
    wl.object_schema AS locked_schema,
    wl.object_name AS locked_table,
    wl.index_name AS locked_index,
    wl.lock_type AS lock_type,
    wl.lock_mode AS lock_mode
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.INNODB_TRX b 
    ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.INNODB_TRX r 
    ON r.trx_id = w.requesting_engine_transaction_id
INNER JOIN performance_schema.data_locks wl 
    ON wl.engine_transaction_id = r.trx_id;

查询结果中：
可以看到锁类型，锁的模式，以及锁涉及的表和索引

lock_type	lock_mode
TABLE	IX
RECORD	X,GAP,INSERT_INTENTION

2.锁类型分析

系统存在以下锁类型：

1. 表级意向排他锁（IX）：
   TABLE | IX

   • 表示事务打算在表的某些行上设置排他锁
   • 是InnoDB的多粒度锁定机制的一部分

2. 记录锁和间隙锁：
   RECORD | X,GAP,INSERT_INTENTION

   • X：排他记录锁（行锁）
   • GAP：间隙锁，锁定索引记录之间的间隙
   • INSERT_INTENTION：插入意向锁

3.问题原因分析

1. 索引问题：
   按 blocking_query字段的查询语句，SQL涉及具体查询业务信息，不展示。
   用explain 显示SQL用到的索引情况，是否符合表的设计和查询的需求。
   由于是多表联合查询，发现有一个表，可以使用组合索引，效率更高，删除索引后，重建组合索引。

2. 优化调整InnoDB参数：

-- 增加锁等待超时时间
SET GLOBAL innodb_lock_wait_timeout = 120;

-- 调整缓冲池大小（根据服务器内存调整）
SET GLOBAL innodb_buffer_pool_size = 8G;

-- 优化并发线程设置 根据CPU核心数调整
SET GLOBAL innodb_thread_concurrency = 16;