1、问题背景：
线上服务日志报数据库连接太多错误，登陆腾讯云控制台查看实例监控及诊断，存在大量update语句行锁等待，1分钟内MySQL连接数从100多突增到400多，CPU占用率从4%飙到100%被打爆，不到10分钟连接数（最大连接数1000）被打满。
最终原因定位为：业务update语句存在行锁等待，短时间内大量重试(频率10Hz)导致实例CPU打满，随后最大连接数打满。持续kill掉等待SQL语句后，故障恢复。
2、问题分析：
MySQL中的行锁是通过InnoDB存储引擎实现的，它使用了多版本并发控制（MVCC）技术。当一个事务需要锁定某一行时，它会首先检查意向锁，如果存在冲突的意向锁，事务会知道将来可能会遇到冲突，并可以决定如何处理（例如等待或者抛出错误）。此外，InnoDB还使用了间隙锁和next-key锁来避免幻读和确保范围内的行被锁定。当然，由于多个事务之间的锁定，也可能会出现死锁的情况。InnoDB有内部机制来检测这些死锁，并通常会终止其中一个事务，以便让另一个事务继续执行。
通常情况下，持有该互斥行锁的会话会迅速的执行完相关操作并释放掉持有的互斥锁（事务提交或者回滚），然后等待的会话在行锁等待超时时间内获得该互斥行锁，进行下一步操作。但在某些情况下，比如一个实例未感知到的来自客户端应用的数据库会话中断，持有该互斥行锁的会话长时间不释放该互斥行锁，此时如果有其他会话申请该互斥行锁，则会导致大量的行锁等待与行锁等待超时。
查看最初锁等待报警信息如下：
对于 5.7 版本，出现锁等待时，我们可以查看 information_schema 中的几张系统表来查询事务状态。
innodb_trx 当前运行的所有事务。
innodb_locks 当前出现的锁。
innodb_lock_waits 锁等待的对应关系
1.执行以下SQL语句，查看正在执行的事务。
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; # 锁等待发生时,查看innodb_trx表可以看到所有事务 # trx_state值为LOCK WAIT 则代表该事务处于等待状态 # innodb_trx 表的每个字段解释： trx_id：事务ID。 trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。 trx_started：事务开始时间。 trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。 trx_wait_started：事务开始等待的时间。 trx_weight：事务的权重。 trx_mysql_thread_id：事务线程 ID，可以和 PROCESSLIST 表 JOIN。 trx_query：事务正在执行的 SQL 语句。 trx_operation_state：事务当前操作状态。 trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。 trx_tables_locked：当前执行 SQL 的行锁数量。 trx_lock_structs：事务保留的锁数量。 trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。 trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。 trx_rows_modified：事务更改的行数。 trx_concurrency_tickets：事务并发票数。 trx_isolation_level：当前事务的隔离级别。 trx_unique_checks：是否打开唯一性检查的标识。 trx_foreign_key_checks：是否打开外键检查的标识。 trx_last_foreign_key_error：最后一次的外键错误信息。 trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。 trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。
2.执行以下SQL语句，查看正在锁的事务。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; # innodb_locks 表的每个字段解释： lock_id：锁 ID。 lock_trx_id：拥有锁的事务 ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。 lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。 lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。 lock_table：被锁定的或者包含锁定记录的表的名称。 lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。 lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。 lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。 lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。 lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。
3.执行以下SQL语句，查看等待锁的事务。
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; # innodb_lock_waits 表的每个字段解释： requesting_trx_id：请求事务的 ID。 requested_lock_id：事务所等待的锁定的 ID。可以和 INNODB_LOCKS 表 JOIN。 blocking_trx_id：阻塞事务的 ID。 blocking_lock_id：某一事务的锁的 ID，该事务阻塞了另一事务的运行。可以和 INNODB_LOCKS 表 JOIN。
4.sys.innodb_lock_waits 视图也可看到事务等待状况，且给出了杀链接的SQL
select * from sys.innodb_lock_waits\G
3、锁等待的处理步骤：
# innodb_locks 表和 innodb_lock_waits 表结合： SELECT * FROM innodb_locks WHERE lock_trx_id IN (SELECT blocking_trx_id FROM innodb_lock_waits); # innodb_locks 表 JOIN innodb_lock_waits 表: SELECT innodb_locks.* FROM innodb_locks JOIN innodb_lock_waits ON (innodb_locks.lock_trx_id = innodb_lock_waits.blocking_trx_id); # 查询 innodb_trx 表: SELECT trx_id, trx_requested_lock_id, trx_mysql_thread_id, trx_query FROM innodb_trx WHERE trx_state = 'LOCK WAIT'; # trx_mysql_thread_id 即kill掉事务线程 ID SHOW ENGINE INNODB STATUS ; # 通过执行这个命令，可以获取InnoDB存储引擎的状态信息，其中包括最近发生的死锁信息。 SHOW FULL PROCESSLIST ; # 从上述方法中得到了相关信息，我们可以得到发生锁等待的线程 ID，然后将其 KILL 掉。 KILL 掉发生锁等待的线程。 kill ID;
为了避免等待行锁，可以考虑以下几点：
使用合适的事务隔离级别，例如READ COMMITTED或者REPEATABLE READ，以减少锁的竞争。
尽量减少事务的长度，让事务尽可能小，不要将复杂逻辑放进一个事务里避免长时间占用锁资源。
在设计数据库表结构时，尽量避免频繁更新同一行的数据，减少锁的竞争。
涉及多行记录时，约定不同事务以相同顺序访问。
业务中要及时提交或者回滚事务，可减少死锁产生的概率。
表要有合适的索引。
参考：
