MySQL产生死锁？！不要慌，问题不大《下》本文接着上篇继续讲解MySQL出现锁竞争问题时的排查手段，涉及如何查看当前锁

简介

本文接着上篇 MySQL产生死锁？！不要慌，问题不大《上》，继续讲解一下MySQL出现锁竞争问题时排查的一些手段

查看阻塞SQL

开启事务1，尝试修改id为1534756548502724610这条记录的nickname

begin;
update user set nickname = '123abcde' where id = 1534756548502724610;
# commit; 不提交事务

开启事务2，也尝试修改id为1534756548502724610这条记录的nickname

begin;
update user set nickname = '123abcde' where id = 1534756548502724610;
# commit; 不提交事务

事务2的SQL执行开始阻塞，陷入等待

最终引发锁等待超时，MySQL自动结束事务2

在阻塞期间可以使用 show processlist;或者 show full processlist; 查看当前被阻塞的SQL。

列出来的信息有用户名，Host,数据库名称,等待时间,信息等...连具体的SQL都列出来了，我们就可以根据需要自行调整业务代码。

在条件允许的情况下，还可以执行 kill命令终止当前阻塞的线程。

比如在这里执行kill 8; (8即show processlist中的Id字段值)。表示结束当前事务2的这个陷入等待的线程。随即Navicat显示Lost connection to server during query。

查看当前事务

再次模拟事务1和事务2场景，事务陷入等待时，我们还可以查询数据表中的事务信息进行问题定位

select * from information_schema.innodb_trx;

执行SQL我们可以看到事务id为69207的这个事务陷入了LOCK_WAIT 锁等待状态

trx_id 事务id

trx_wait_started 锁等待开始时间 为 2025-01-04 17:53:14 ，我们可以根据这个时间和当前时间推断出这个事务已经等待了多久。

trx_state 事务执行状态

trx_mysql_thread_id 为执行该事务的mysql线程id, 值为17

此时执行show processlist;

可以看到刚好存在一条Id为17的记录，没错，这里的Id恰好对应的就是information_schema.innodb_trx表的trx_mysql_thread_id

其中一些比较关键的信息都能帮我们更好地排查问题，这里不一一列举了

trx_tables_in_use 处理当前事务SQL语句时使用的表数量

trx_query 执行的SQL语句

trx_tables_locked 具有行锁的表数量

trx_lock_structs 事务使用的锁数量

trx_rows_locked 事务锁住的大致行数

trx_rows_modified 事务中修改和插入的行数

trx_isolation_level 当前事务隔离级别

Tips: 有时候可能会出现查询innodb_trx表，trx_state为LOCK_WAIT 锁等待，但是trx_query SQL语句这一列却空空如也的情况。再根据trx_mysql_thread_id执行show processlist查询相应的线程信息，发现Command这一列为Sleep。。。

那么大概率是团队成员在业务代码中犯了低级到不能再低级的错误，他们开启了一个事务，但是事务里并不是执行SQL操作后立即提交，而是在忙着各种处理代码逻辑，发起Http请求，RPC请求。。

导致出现了mysql线程本身很空闲，但是又的确有个长事务在阻塞等待，影响表读写操作的现象。

感兴趣的可以移步五个例子品尝坏代码的味道

查看当前没有释放的锁

执行事务1的SQL

begin;
update user set nickname = '123abcde' where id = 1534756548502724610;
# commit; 不提交事务

执行如下SQL，通过查询data_locks表，可以查询当前所有没有释放的锁(行锁、表锁)

select * from performance_schema.data_locks;

LOCK_TYPE 锁的级别 TABLE 表级别的锁 RECORD 行级别的锁

LOCK_MODE 锁定模式 IX意向排他锁 X排他锁

LOCK_STATUS 锁状态 GRANTED 已被授权

LOCK_DATA 锁定索引的值

INDEX_NAME 索引名称 PRIMARY主键索引或者别的索引名称，比如idx_user_phone

查看当前锁等待信息

重现事务1和事务2的步骤，事务2等待锁时，执行如下SQL

select * from performance_schema.data_lock_waits;

显示出当前所锁等待信息

其中一些字段

REQUESTING_ENGINE_LOCK_ID 正在请求锁定的事务信息

BLOCKING_ENGINE_TRANSACTION_ID 持有阻塞锁的事务信息

BLOCKING_ENGINE_TRANSACTION_ID 为69211，此时查询innodb_trx表可以看到也有一条trx_id为69211的记录。有时我们可以写SQL进行关联查询得到许多信息

Tips： data_locks表只是记录一些锁的信息。而data_lock_waits表是用来记录锁之间等待以及依赖关系。

查看正在执行的 SQL 语句的信息

前面通过data_lock_waits表能得到的信息不多，我们还可以通过查询events_statements_current表得到更多信息。该表记录的是MySQL 服务器正在执行的 SQL 语句的实时信息。它包含了关于当前执行的每个 SQL 语句的详细信息，如语句的执行时间、是否被阻塞、是否处于等待状态等

select * from performance_schema.events_statements_current;

TIMER_START 开始时间

TIMER_END 结束时间

SQL_TEXT 执行的SQL

还可以执行如下语句将data_lock_waits表和events_statements_current表进行关联，得到持有阻塞锁的事务所执行的SQL

SELECT esc.sql_text
FROM performance_schema.data_lock_waits dlw
         JOIN performance_schema.events_statements_current esc ON dlw.BLOCKING_THREAD_ID = esc.thread_id;

或者正在请求锁定的事务所执行的SQL

SELECT esc.sql_text
FROM performance_schema.data_lock_waits dlw
         JOIN performance_schema.events_statements_current esc ON dlw.REQUESTING_THREAD_ID = esc.thread_id;

查看Innodb状态

先手动产生一个死锁执行事务1

begin; # 步骤1
select * from user where mobile = '13961884811' for update ;  # 步骤2
select * from user where mobile = '13811112222' for update ;  # 步骤4

执行事务2

begin;
select * from user where mobile = '13811112222' for update ;  # 步骤3
select * from user where mobile = '13961884811' for update ;  # 步骤5

触发死锁检测

通过执行以下SQL查询InnoDB的当前状态和详细的内部信息

SHOW ENGINE INNODB STATUS;

可以看到最近的一次死锁信息的详细信息

WE ROLL BACK TRANSACTION (2)  表示MySQL自动回滚了TRANSACTION (2)

查看元数据锁

可以通过查询metadata_locks表查看元数据锁信息

select * from performance_schema.metadata_locks;

查看当前每个打开的表所持有的表锁

查询table_handles表则可以查看当前每个打开的表所持有的表锁信息，简单点就是记录表句柄的信息。这张表相对查询用得比较少。

select * from performance_schema.table_handles

OBJECT_SCHEMA 库名

OBJECT_NAME 对象名称

OWNER_THREAD_ID 拥有表句柄的线程

OWNER_EVENT_ID 当前操作该表句柄的事件的 ID

总结

以上便是MySQL排查死锁时可以用到的一些方法，因为单篇写下来篇幅太长的缘故，分成了上下两篇，还望见谅，希望能对你有所帮助。