RDS for MySQL InnoDB 行锁等待和锁等待超时的处理-阿里云开发者社区

开发者社区> 阿里云支持与服务> 正文

RDS for MySQL InnoDB 行锁等待和锁等待超时的处理

简介: RDS for MySQL InnoDB 行锁等待和锁等待超时的处理   1. Innodb 引擎表行锁等待和等待超时发生的场景 2.Innodb 引擎行锁等待情况的处理 2.1 Innodb 行锁等待超时参数 innodb_lock_wait_timeout 2.2 大量

RDS for MySQL InnoDB 行锁等待和锁等待超时的处理

 

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

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

2.1 InnoDB 行锁等待超时参数 innodb_lock_wait_timeout

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


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

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

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

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

2. 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 锁等待查看,如下图:

innodb_lock_wait_02.png

方法 2:通过 DMS  实例信息  实例会话查看,如下图

innodb_lock_wait_04.png

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

# RDS for MySQL 5.6 / 5.7select 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_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
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 操作,导致行锁等待和行锁等待超时),确认业务可以接受其对应的事务回滚的情况下,可以将其终止。

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档