<a href="#1">1. innodb 引擎表行锁等待和等待超时发生的场景</a>
<a href="#2">2.innodb 引擎行锁等待情况的处理</a>
<a href="#2.1">2.1 innodb 行锁等待超时参数 innodb_lock_wait_timeout</a>
<a href="#2.2">2.2 大量行锁等待和行锁等待超时的处理</a>
当一个 rds for mysql 连接会话等待另外一个会话持有的互斥行锁时,会发生 innodb 引擎表行锁等待情况。
通常情况下,持有该互斥行锁的会话(连接)会迅速的执行完相关操作并释放掉持有的互斥锁(事务提交或者回滚),进而等待的会话在行锁等待超时时间到来前获得该互斥行锁,进行下一步操作。
但在某些情况下,比如一个实例未感知到的来自客户端应用的数据库会话中断,持有该互斥行锁的会话长时间不释放该互斥行锁,此时如果有其他会话申请该互斥行锁,则会导致大量的行锁等待与行锁等待超时。
本文提供的检查和处理方法,仅当正在发生 innodb 行锁等待的情况下才成立;因为 innodb 行锁等待默认超时时间为 50 秒,因此通常情况下不容易观察到行锁等待现场,可以通过将 innodb_lock_wait_timeout 参数设置为较大值来复现问题(生产环境不推荐使用过大的 innodb_lock_wait_timeout 参数值)。
#
参数
默认值
最小值
最大值
说明
1
innodb_lock_wait_timeout
50
1073741824
获取innodb 行锁的等待时间,单位秒。可在会话级别设置
该参数控制 innodb 行锁等待的超时时间,单位为秒,rds 实例该参数的默认值为 50(秒)。
等待互斥锁的会话在等待 50 秒后会退出锁等待状态并返回下面的错误,这个行为称之为 innodb 引擎表行锁等待超时。
可以通过下面的命令查看当前会话和全局的参数设置。
该参数支持在会话级别修改,方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间,如下:
如果行锁等待和行锁等待超时持续发生,并且导致当前应用运行异常,那么需要获取到一直持有行锁的会话,并且终止该会话来释放持有的锁(会话对应的事务会回滚)。
注:
下面的方法必须在行锁等待正在发生的时候进行检查。
方法 1: 通过 dms
实例信息
innodb 锁等待查看,如下图:
方法 2:通过 dms
实例会话查看,如下图
方法 3: 在 dms 无法登录的情况下,可以通过执行下面的查询,获得导致行锁等待和行锁等待超时的会话。
比如:
对于复杂的多个会话相互行锁等待情况,建议先终止 role 为 blocker 且 trx_state 为 running 的会话;终止后再次检查,如果仍旧有行锁等待,再终止新结果中的 role 为 blocker 且 trx_state 为 running 的会话。
对于标识为 blocker 的会话(持有锁阻塞其他会话的 dml 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。
比如,可以通过 kill 命令来今后会话终止。