手把手教你定位线上MySQL锁超时问题

向右看齐 2024-04-06 09:53 77阅读 0赞

昨晚我正在床上睡得着着的，突然来了一条短信。

![748356c7d97d45bf8c537e86eb7b0fe5.png][]

什么？线上的订单无法取消！

我赶紧登录线上系统，查看业务日志。

![c4fa9da0a4434692ab3c3a7f965f60ce.png][]

发现有MySQL锁超时的错误日志。

不用想，肯定有另一个事务正在修改这条订单，持有这条订单的锁。

导致当前事务获取不到锁，一直等待，直到超过锁超时时间，然后报错。

既然问题已经清楚了，接下来就轮到怎么排查一下到底是哪个事务正在持有这条订单的锁。

好在MySQL提供了丰富的工具，帮助我们排查锁竞争问题。

现场复现一个这个问题：

创建一张用户表，造点数据：

CREATE TABLE \`user\` (  
\`id\` int(11) NOT NULL AUTO\_INCREMENT COMMENT '主键ID',  
\`name\` varchar(50) NOT NULL DEFAULT '' COMMENT '姓名',  
PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  
事务1，更新id=1的用户姓名，不提交事务：

begin;  
update user set name='一灯' where id=1;  
事务2，删除id=1的数据，这时候会产生锁等待：

begin;  
delete from user where id=1;  
接下来，我们就通过MySQL提供的锁竞争统计表，排查一下锁等待问题：

先查一下锁等待情况：

select \* from information\_schema.innodb\_lock\_waits;

![4f2500a062d04effbd9ba01105051a89.png][]  
可以看到有一个锁等待的事务。

然后再查一下正在竞争的锁有哪些？

select \* from information\_schema.innodb\_locks;

![263b93ac3def4b599287de42ef8c6725.png][]  
可以看到，MySQL统计的非常详细：

lock\_trx\_id 表示事务ID  
lock\_mode 表示排它锁还是共享锁  
lock\_type 表示锁定的记录，还是范围  
lock\_table 锁的表名  
lock\_index 锁定的是主键索引  
再查一下正在执行的事务有哪些？

select \* from information\_schema.innodb\_trx;

![f66ffa93cf96428295d099ccbe46ff9f.png][]  
可以清楚的看到正在执行的事务有两个，一个状态是锁等待（LOCK WAIT），正在执行的SQL也打印出来了：

delete from user where id=1;  
正是事务2的删除语句。

不用问，第二条，显示正在运行状态（RUNNING）的事务就是正在持有锁的事务1，MySQL线程id（trx\_mysql\_thread\_id）是193。

我们用MySQL线程id查一下事务线程id：

select \* from performance\_schema.threads where processlist\_id=193;

![4b6d8a97aabd49d5938e5e389b86b72d.png][]  
找到对应的事务线程id是218，然后再找一下这个线程正在执行的SQL语句：

select THREAD\_ID,CURRENT\_SCHEMA,SQL\_TEXT  
from performance\_schema.events\_statements\_current  
where thread\_id=218;

![2cb3cb7fe635475c8d8fcdd51ee04112.png][]  
可以清楚的看到这个线程正在执行的SQL语句就是事务1的update语句。

持有锁的SQL语句找到了，接下来再去找对应的业务代码也就轻而易举了。

以上是基于MySQL5.7版本，在MySQL8.0版本中有些命令已经删除了，替换成了其他命令，下篇文章再讲一下MySQL8.0怎么定位MySQL锁超时问题。

[748356c7d97d45bf8c537e86eb7b0fe5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/c973efde10cf40649e9204c432fb77e5.png
[c4fa9da0a4434692ab3c3a7f965f60ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/508c07c380a74e0e930c20ada0526fcf.png
[4f2500a062d04effbd9ba01105051a89.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e0ba9c062fc042ba9d3ac9b26af065bc.png
[263b93ac3def4b599287de42ef8c6725.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/52726f37778a48da92e8207d9481c84d.png
[f66ffa93cf96428295d099ccbe46ff9f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/4866b449234545d9a81284337c1c57aa.png
[4b6d8a97aabd49d5938e5e389b86b72d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/59971f235979463a8d32395d202e68d0.png
[2cb3cb7fe635475c8d8fcdd51ee04112.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e24bcb3c34614918b822d922f242d72d.png