一文教你学会定位线上MySQL锁超时问题

以下是“一文教你学会定位线上MySQL锁超时问题”的完整攻略。

问题背景

在MySQL中，为了保证并发性，当某个事务要修改数据时，会自动为要修改的行加上锁，防止其他事务同时访问，这种锁就叫做行锁。而当一些事务互相等待对方释放锁时，就会产生死锁，这时MySQL会自动检测到死锁，并选择其中一个事务进行回滚以解锁。

然而，在极端情况下，如果某个事务一直无法获得所需要的锁，就会出现锁超时，导致事务无法继续执行。本文将介绍如何定位线上MySQL锁超时问题。

方案

第一步：查看MySQL进程列表

通过show processlist命令查看MySQL当前的进程列表，找到正在执行的等待状态的进程，记录下它的ID。

示例1：

mysql> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+
| Id | User | Host      | db   | Command | Time | State                           |
+----+------+-----------+------+---------+------+---------------------------------+
| 1  | root | localhost | NULL | Query   | 0    | starting                       |
| 2  | root | localhost | db1  | Sleep   | 0    |                                 |
| 3  | root | localhost | db1  | Sleep   | 0    |                                 |
| 4  | root | localhost | db1  | Query   | 10   | Waiting for table metadata lock |
+----+------+-----------+------+---------+------+---------------------------------+

其中，进程ID为4的进程处于等待表元数据锁的状态。如果长期处于等待状态，则极有可能出现锁超时问题。

第二步：查看进程的等待状态

通过show engine innodb status\G命令查看该进程的详细信息，找到“LATEST DETECTED DEADLOCK”一节，查看到达死锁的时间和死锁关键词（lock_key）。

示例2：

--------LATEST DETECTED DEADLOCK--------
2018-08-30 15:09:53 7f663d8f5700
*** (1) TRANSACTION:
TRANSACTION 996221511, ACTIVE 19 sec starting index read
mysql tables in use 7, locked 7
LOCK WAIT 6 lock struct(s), heap size 2936, 3 row lock(s)
MySQL thread id 108483233, OS thread handle 5115, query id 17460124 localhost root Sending data
SELECT COUNT(*) FROM table1 WHERE `name` LIKE '%xxx%'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1843 page no 5691 n bits 1280 index PRIMARY of table `db1`.`table1` trx id 996221511 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
...

*** (2) TRANSACTION:
TRANSACTION 996221500, ACTIVE 19 sec starting index read
mysql tables in use 7, locked 7
6 lock struct(s), heap size 2936, 3 row lock(s)
MySQL thread id 108482626, OS thread handle 5109, query id 17460108 localhost root Sending data
SELECT COUNT(*) FROM table2 WHERE `name` LIKE '%xxx%'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1843 page no 35042 n bits 1280 index PRIMARY of table `db1`.`table2` trx id 996221500 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1843 page no 4578 n bits 1280 index PRIMARY of table `db1`.`table1` trx id 996221500 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
...

可以看到，该进程正在执行SELECT COUNT(*) FROM table1 WHEREnameLIKE '%xxx%'这条查询语句，而同时也需要获取db1.table1表上的行锁。然而，该锁已经被进程ID为996221500的事务占用，导致出现锁等待，最终导致了死锁。

第三步：查看锁等待的时间

通过查看该进程的状态机（State Machine）可以得知该进程等待锁的时间，如果等待时间过长（如10秒以上），就有可能出现锁超时问题。还可以通过innodb_lock_wait_timeout系统变量来设置锁等待超时时间，以避免出现锁等待时间过长的情况。

结束语

在以上攻略的指引下，相信读者可以更好地定位线上MySQL锁超时问题，及时解决问题，保证系统的稳定性和高可用性。

示例1描述了使用show processlist命令查看MySQL当前进程的列表，示例2详细讲解了通过show engine innodb status\G命令查看当前进行执行的等待状态，以及查看进程等待的时间。