摘自阿里 rds

RDS MySQL InnoDB 锁等待和锁等待超时的处理
更新时间：2017-08-10 13:51:23 分享：
1. Innodb 引擎表行锁等待和等待超时发生的场景

2.Innodb 引擎行锁等待情况的处理

2.1 Innodb 行锁等待超时参数 innodb_lock_wait_timeout

2.2 大量行锁等待和行锁等待超时的处理

1. Innodb 引擎表行锁等待和等待超时发生的场景

当一个 RDS MySQL 连接会话等待另外一个会话持有的互斥行锁时，会发生 Innodb 引擎表行锁等待情况。

通常情况下，持有该互斥行锁的会话（连接）会迅速的执行完相关操作并释放掉持有的互斥锁（事务提交或者回滚），进而等待的会话在行锁等待超时时间到来前获得该互斥行锁，进行下一步操作。

但在某些情况下，比如一个实例未感知到的来自客户端应用的数据库会话中断，持有该互斥行锁的会话长时间不释放该互斥行锁，此时如果有其他会话申请该互斥行锁，则会导致大量的行锁等待与行锁等待超时。

1. Innodb 引擎行锁等待情况的处理

本文提供的检查和处理方法，仅当正在发生 InnoDB 行锁等待的情况下才成立；因为 InnoDB 行锁等待默认超时时间为50秒，因此通常情况下不容易观察到行锁等待现场，可以通过将 innodb_lock_wait_timeout 参数设置为较大值来复现问题（生产环境不推荐使用过大的 innodb_lock_wait_timeout 参数值）。

2.1. Innodb 行锁等待超时参数 innodb_lock_wait_timeout

参数 默认值 最小值 最大值 说明

1 innodb_lock_wait_timeout 50 1 1073741824 获取Innodb 行锁的等待时间，单位秒。可在会话级别设置
该参数控制 Innodb 行锁等待的超时时间，单位为秒，RDS 实例该参数的默认值为 50（秒）。

等待互斥锁的会话在等待 50 秒后会退出锁等待状态并返回下面的错误，这个行为称之为 Innodb 引擎表行锁等待超时。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_lock_wait_00.png

可以通过下面的命令查看当前会话和全局的参数设置。

show variables like ‘innodb_lock_wait_timeout’; — 查看当前会话show global variables like ‘innodb_lock_w%’; — 查看全局设置
该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：

set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒
innodb_lock_wait_01.png

2.2. 大量行锁等待和行锁等待超时的处理

如果行锁等待和行锁等待超时持续发生，并且导致当前应用运行异常，那么需要获取到一直持有行锁的会话，并且终止该会话来释放持有的锁（会话对应的事务会回滚）。

2.2.1 检查导致锁等待和锁超时的会话

注：下面的方法必须在行锁等待正在发生的时候进行检查。

方法 1： 通过 DMS 实例信息 Innodb 锁等待查看。

方法 2：通过 DMS 实例信息 实例会话查看。

方法 3： 在 DMS 无法登录的情况下，可以通过执行下面的查询，获得导致行锁等待和行锁等待超时的会话。

select l.* from ( select ‘Blocker’ role, p.id, p.user, left(p.host, locate(‘:’, p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, lw.requesting_thd_id Blockee_id, lw.requesting_trx_id Blockee_trx from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union select ‘Blockee’ role, p.id, p.user, left(p.host, locate(‘:’, p.host) - 1) host, tx.trx_id, tx.trx_state, tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index, tx.trx_query, null, null from information_schema.innodb_trx tx, information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo, information_schema.processlist p where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;
比如：

对于复杂的多个会话相互行锁等待情况，建议先终止 Role 为 Blocker 且 trx_state 为 RUNNING 的会话；终止后再次检查，如果仍旧有行锁等待，再终止新结果中的 Role 为 Blocker 且 trx_state 为 RUNNING 的会话。

2.2.2 处理导致行锁等待和行锁等待超时的会话

对于标识为 Blocker 的会话（持有锁阻塞其他会话的 DML 操作，导致行锁等待和行锁等待超时），确认业务可以接受其对应的事务回滚的情况下，可以将其终止。

终止会话的方法请参考：RDS for MySQL如何终止会话。

比如，可以通过 Kill 命令来今后会话终止。

最后

以上就是真实月光为你收集整理的RDS MySQL InnoDB 锁等待和锁等待超时的处理参数 默认值 最小值 最大值 说明的全部内容，希望文章能够帮你解决RDS MySQL InnoDB 锁等待和锁等待超时的处理参数 默认值 最小值 最大值 说明所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。