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

昨晚我在床上睡着了,突然来了一条短信。

[En]

I was asleep in bed last night when suddenly a text message came.

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

什么?网上订单不能取消!

[En]

What? The online order cannot be cancelled!

我迅速登录了在线系统,并查看了业务日志。

[En]

I quickly logged on to the online system and checked the business log.

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

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

想都别想,肯定还有另一笔交易在修改这个订单,持有这个订单的锁。

[En]

Don’t think about it, there must be another transaction modifying this order, holding the lock of this order.

导致当前事务无法获取锁,等待直到超过锁定超时,然后报告错误。

[En]

Causes the current transaction to fail to acquire the lock, wait until the lock timeout is exceeded, and then report an error.

现在问题已经清楚了,是时候找出是哪个事务控制了订单。

[En]

Now that the problem is clear, it’s time to figure out which transaction is holding the lock on the order.

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

现场复现一个这个问题:

创建用户表并创建一些数据:

[En]

Create a user table and create some data:

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;

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

您可以看到有一个事务正在等待锁定。

[En]

You can see that there is a transaction waiting for a lock.

然后检查哪些锁在竞争中?

[En]

Then check which locks are in competition?

select * from information_schema.innodb_locks;

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

可以看到,MySQL统计的非常详细:

lock_trx_id 表示事务ID
lock_mode 表示排它锁还是共享锁
lock_type 表示锁定的记录,还是范围
lock_table 锁的表名
lock_index 锁定的是主键索引

再次检查正在执行哪些交易?

[En]

Check again what transactions are being performed?

select * from information_schema.innodb_trx;

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

可以清楚的看到正在执行的事务有两个,一个状态是锁等待( 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;

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

找到对应的事务线程id是218,然后再找一下这个线程正在执行的SQL语句:

select THREAD_ID,CURRENT_SCHEMA,SQL_TEXT
from performance_schema.events_statements_current
where thread_id=218;

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

可以清楚的看到这个线程正在执行的SQL语句就是事务1的update语句。

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

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

文章持续更新,可以微信搜一搜「 一灯架构 」第一时间阅读更多技术干货。

Original: https://www.cnblogs.com/yidengjiagou/p/16575614.html
Author: 一灯架构
Title: 手把手教你定位线上MySQL锁超时问题,包教包会

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505056/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球