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

问题描述：


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


解决过程：


当一个RDS MySQL连接会话等待另外一个会话持有的互斥锁时，会发生Innodb锁等待情况。
通常情况下，持有该互斥锁的会话（连接）会迅速的执行完相关操作并释放掉持有的互斥锁，进而等待的会话在锁等待超时时间到来前获得该互斥锁，进行下一步操作。
但在某些情况下，比如一个实例未感知到的RDS中断会话，持有该互斥锁的会话长时间不释放该互斥锁，可能会导致大量的锁等待与锁等待超时。
Innodb锁等待情况的处理：
1. Innodb锁等待超时参数innodb_lock_wait_timeout
该参数控制Innodb锁等待的超时时间，单位为秒，RDS实例该参数的默认值为50（秒）。
等待互斥锁的会话在等待50秒后会退出锁等待状态并返回 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 错误，如下图：

该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的锁等待超时时间，如下图：

注：本文提供的检查和处理方法，仅当正在发生 InnoDB 锁等待的情况下才成立；因为InnoDB锁等待默认超时时间为50秒，因此通常情况下不容易观察到现场，可以通过将 innodb_lock_wait_timeout 参数设置为较大值来复现问题（生产环境不推荐使用过大的innodb_lock_wait_timeout值）。
2. 大量锁等待和锁等待超时的处理
2.1 检查导致锁等待和锁超时的会话
2.1.1 通过 IDB Cloud =》实例信息 =》 Innodb 锁等待 查看，如下图：

2.1.2 通过 IDB Cloud => 实例信息 =》实例会话查看，如下图

2.1.3 在IDB CLOUD 无法登陆的情况下，可以通过MySQL命令行工具执行下面的代码，获得导致锁等待和锁超时的会话。
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,
    tx.trx_tables_in_use,
    tx.trx_tables_locked,
    tx.trx_rows_locked
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_trx_id = tx.trx_id
union all
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,
    tx.trx_tables_in_use,
    tx.trx_tables_locked,
    tx.trx_rows_locked
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_trx_id = tx.trx_id \G
比如：

2.2 处理导致锁等待和锁超时的会话
2.2.1 通过 IDB Cloud =》实例信息 =》 实例会话 kill 掉导致锁等待的会话
2.2.2 在IDB CLOUD 无法登陆的情况下，可以通过MySQl命令行工具执行 kill 命令，来终止导致锁等待的会话，如下图：