如何查看MySQL/MariaDB InnoDB中SQL查询执行时的锁列表？

嘿，刚好对InnoDB锁查询这块摸得比较透，给你拆解下这两个问题的解决办法：

1. 如何通过MySQL查询查看锁列表？ #

查看MySQL里的锁列表，主要分两种情况，取决于你的MySQL/MariaDB版本：

• MySQL 5.7及以上/ MariaDB 10.2及以上：优先用performance_schema里的视图，这是官方推荐的方式，信息更全也更高效：
  执行下面的查询就能获取所有锁的详细信息，包括锁类型、关联的表/行、持有锁的线程ID等：

  SELECT 
    OBJECT_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA,
    THREAD_ID
  FROM performance_schema.data_locks;
  

  如果想过滤出某个表的锁，加个WHERE OBJECT_NAME = '你的表名'就行。

• 旧版本（MySQL 5.6及以下/MariaDB 10.1及以下）：用INFORMATION_SCHEMA.INNODB_LOCKS表，不过这个表在新版本里已经被标记为废弃了：

  SELECT 
    lock_id,
    lock_trx_id,
    lock_mode,
    lock_type,
    lock_table,
    lock_index,
    lock_data
  FROM INFORMATION_SCHEMA.INNODB_LOCKS;
  

另外，还可以用SHOW ENGINE INNODB STATUS命令，输出里的TRANSACTIONS部分会包含当前的锁等待情况，不过这个输出是文本格式，需要自己找对应的内容，适合快速排查锁等待问题。

2. 执行大型SQL查询时，如何查看其在MySQL/MariaDB InnoDB引擎中设置的全部锁列表？ #

要定位到某条大型SQL持有的所有锁，步骤如下：

1. 先找到这条SQL对应的线程ID（PROCESSLIST_ID）
   执行SHOW FULL PROCESSLIST，找到那条大型SQL的Id列值，或者用performance_schema.processlist来精准过滤：

   SELECT 
     THREAD_ID,
     PROCESSLIST_ID,
     PROCESSLIST_INFO
   FROM performance_schema.processlist
   WHERE PROCESSLIST_INFO LIKE '%你的大型SQL关键词%';
   

2. 关联查询该线程持有的所有锁
   拿到THREAD_ID或者PROCESSLIST_ID后，结合performance_schema.data_locks查询：

   SELECT 
     dl.OBJECT_NAME,
     dl.LOCK_TYPE,
     dl.LOCK_MODE,
     dl.LOCK_DATA,
     pl.PROCESSLIST_INFO
   FROM performance_schema.data_locks dl
   JOIN performance_schema.processlist pl ON dl.THREAD_ID = pl.THREAD_ID
   WHERE pl.PROCESSLIST_ID = '你找到的进程ID';
   

3. 如果是旧版本
   用INFORMATION_SCHEMA.INNODB_LOCKS关联INNODB_TRX，先找到SQL对应的事务ID，再查锁：

   SELECT 
     il.lock_table,
     il.lock_mode,
     il.lock_data
   FROM INFORMATION_SCHEMA.INNODB_LOCKS il
   JOIN INFORMATION_SCHEMA.INNODB_TRX it ON il.lock_trx_id = it.trx_id
   WHERE it.trx_query LIKE '%你的大型SQL关键词%';
   

另外，要是这条大型SQL导致了锁等待，SHOW ENGINE INNODB STATUS里的LATEST DETECTED DEADLOCK或者TRANSACTIONS部分会有更详细的锁冲突信息，能帮你快速定位问题。

内容的提问来源于stack exchange，提问作者Florian Mertens