Taught you how to locate online MySQL lock timeout problem, BaoJiao package

携手创作,共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第8天,点击查看活动详情

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

什么？Online orders cannot be cancelled！

我赶紧登录线上系统,查看业务日志.

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

不用想,There must be another transaction modifying this order,Holds the lock on this order.

As a result, the current transaction cannot acquire the lock,一直等待,until the lock timeout is exceeded,然后报错.

既然问题已经清楚了,The next step is to find out which transaction is holding the lock on this order.

好在MySQL提供了丰富的工具,Help us troubleshoot lock contention issues.

This problem is reproduced on site：

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

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的数据,At this time, a lock wait will occur：

begin;
delete from user where id=1;
复制代码

接下来,我们就通过MySQLProvided lock contention statistics table,Troubleshoot lock wait issues：

Check the lock wait first：

select * from information_schema.innodb_lock_waits;
复制代码

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

Then check which locks are competing？

select * from information_schema.innodb_locks;
复制代码

可以看到,MySQLStatistics are very detailed：

lock_trx_id 表示事务ID

lock_mode Indicates an exclusive lock or a shared lock

lock_type Represents a locked record,还是范围

lock_table lock table name

lock_index Locked is the primary key index

Check again what transactions are being executed？

select * from information_schema.innodb_trx; 
复制代码

It can be clearly seen that there are two transactions being executed,One state is lock waiting（LOCK WAIT）,正在执行的SQL也打印出来了：

delete from user where id=1;
复制代码

正是事务2的删除语句.

不用问,第二条,Displays running status（RUNNING）The transaction is the transaction that is holding the lock1,MySQL线程id（trx_mysql_thread_id）是193.

我们用MySQL线程idCheck out transaction threadsid：

select * from performance_schema.threads where processlist_id=193;
复制代码

Find the corresponding transaction threadid是218,Then find out what this thread is executingSQL语句：

select THREAD_ID,CURRENT_SCHEMA,SQL_TEXT 
from performance_schema.events_statements_current 
where thread_id=218;
复制代码

You can clearly see what this thread is doingSQLStatements are transactions1的update语句.

以上是基于MySQL5.7版本,在MySQL8.0Some commands have been removed from the version,replaced with other commands,Let's talk about it in the next articleMySQL8.0怎么定位MySQL锁超时问题.