MySQL 快速找到 锁表 以及正在锁表的SQL的方法

通过 sys.schema_table_lock_waits

# 执行 show processlist，如果 DDL 的状态是 Waiting for table metadata lock ，则意味着这个 DDL 被阻塞了
# 一旦被阻塞了，后续针对该表的所有操作都会被阻塞，都会显示 Waiting for table metadata lock


--  blocking_pid 阻塞的process id，sql_kill_blocking_connection  kill掉阻塞的process id
select blocking_pid,sql_kill_blocking_connection from sys.schema_table_lock_waits

-- 通过这个语句可以找到那个线程被阻塞
select * from sys.schema_table_lock_waits

information_schema 相关SQL

SELECT
 t3.Id --  (PROCESSLIST_ID): 线程的唯一标识符
,t3.User -- 执行查询的MySQL用户名
,t3.Host -- 发起查询的客户端主机名或IP地址
,t3.db -- 当前线程正在操作的数据库名称，如果有的话
,t3.Command --  线程当前的状态，如Sleep, Query, Connect, Fetch等
,t3.Time -- TIME_MS (TIME_MS): 线程处于当前状态的时间长度。在较新的MySQL版本中，TIME_MS以毫秒为单位提供更精确的时间
,t3.State -- 提供关于线程当前更具体状态的额外信息，比如Waiting for table metadata lock
,t3.Info -- 正在执行的SQL语句的一部分。如果是长查询，可能会被截断。使用SHOW FULL PROCESSLIST;可以获取完整的查询语句。
,t1.trx_query -- 正在执行的事务中的SQL语句文本，这对于诊断问题特别有用，因为它直接展示了导致事务行为的SQL代码。
,t1.trx_id --  InnoDB内部生成的事务唯一ID，对于只读事务或未申请锁的事务，不会分配此ID
,t1.trx_state -- 事务的状态，常见的值有RUNNING（事务正在执行）、LOCK WAIT（事务在等待锁）等
,t1.trx_started -- 事务开始的时间戳，帮助判断事务运行的时长。
,t1.trx_requested_lock_id -- 如果事务处于锁等待状态，此字段标识它正在等待的锁的ID。
,t1.trx_wait_started -- 如果事务在等待锁，记录等待开始的时间
,t1.trx_mysql_thread_id -- MySQL线程ID，与执行事务的客户端线程相关联
,t2.requesting_trx_id -- 请求锁但被阻塞的事务ID。这是等待事务的唯一标识
,t2.requested_lock_id -- 请求的锁的 ID 
,t2.blocking_trx_id --  阻塞其他事务的事务 ID
,t2.blocking_lock_id -- 阻塞其他事务的锁的 ID
FROM information_schema.INNODB_TRX t1  
join information_schema.INNODB_LOCK_WAITS t2 on t1.trx_id = t2.blocking_trx_id 
join information_schema.PROCESSLIST t3 on t1.trx_mysql_thread_id = t3.id  

快速解决死锁的办法： 找到死锁的 thread id

kill Id

附录：

-- SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  -- 查看等待锁的事务
-- SELECT * FROM information_schema.INNODB_TRX  -- 查看正在进行中的事务
-- SELECT * FROM information_schema.INNODB_LOCKS -- 查看正在锁的事务

-- show full PROCESSLIST; 等价于以下SQL：
select * from information_schema.PROCESSLIST


查询是否锁表
SHOW OPEN TABLES where In_use > 0;