查询数据库锁死情况

1. 概述

有些情况下，我们在执行SQL的时候，有些SQL被锁死，我需要查询锁的清空，需要如何处理。

2. 创建测试表

CREATE TABLE `t_user` (
  `id` INT NOT NULL PRIMARY KEY,
  `name` VARCHAR(50),
  `balance` DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO t_user VALUES (1, 'Alice', 100.00), (2, 'Bob', 200.00);

3. 锁查询

行锁意思是，两个事务锁住同一行数据的情况.
模拟锁,开启两个事务锁住同一行的情况

-- 执行第一个更新
START TRANSACTION;
update t_user set name='A1' where id=1;

-- 执行第二个更新
START TRANSACTION;
update t_user set name='B1' where id=1;

这样 因为第一个没有提交，第二个就会被锁住

查询锁的情况

SELECT
  waiting_trx.trx_id AS waiting_trx_id,
  waiting_trx.trx_mysql_thread_id AS waiting_thread,
  waiting_stmt.SQL_TEXT AS waiting_sql,
  blocking_trx.trx_id AS blocking_trx_id,
  blocking_trx.trx_mysql_thread_id AS blocking_thread,
  blocking_stmt.SQL_TEXT AS blocking_sql
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks waiting_lock 
  ON w.REQUESTING_ENGINE_LOCK_ID = waiting_lock.ENGINE_LOCK_ID
JOIN performance_schema.data_locks blocking_lock 
  ON w.BLOCKING_ENGINE_LOCK_ID = blocking_lock.ENGINE_LOCK_ID
JOIN performance_schema.threads waiting_thread 
  ON waiting_lock.THREAD_ID = waiting_thread.THREAD_ID
JOIN performance_schema.threads blocking_thread 
  ON blocking_lock.THREAD_ID = blocking_thread.THREAD_ID
JOIN information_schema.innodb_trx waiting_trx 
  ON waiting_trx.trx_mysql_thread_id = waiting_thread.PROCESSLIST_ID
JOIN information_schema.innodb_trx blocking_trx 
  ON blocking_trx.trx_mysql_thread_id = blocking_thread.PROCESSLIST_ID
LEFT JOIN performance_schema.events_statements_current waiting_stmt 
  ON waiting_stmt.THREAD_ID = waiting_lock.THREAD_ID
LEFT JOIN performance_schema.events_statements_current blocking_stmt 
  ON blocking_stmt.THREAD_ID = blocking_lock.THREAD_ID;

通过上面SQL查询结果如下：