mysql | 如何快速定位持有锁事务？

事实上在线上有锁发生的时候，我们应该快速定位并处理它，因此提前准备一些脚本是非常有必要的。

查看哪些事务正在等待哪些事务正在阻塞

### mysql5
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query 
FROM
  information_schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

### mysql8
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query 
FROM
  performance_schema.data_lock_waits w
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

尝试获取锁源 sql

### 5.7 & 8.0 
### performance_schema.events_statements_current 
### 可以确认线程的最后一个查询
 select a.sql_text,
       c.id,
       d.trx_started,a.thread_id
  from performance_schema.events_statements_current a 
  join performance_schema.threads b ON a.thread_id= b.thread_id 
  join information_schema.processlist c ON b.processlist_id= c.id 
  join information_schema.innodb_trx d ON c.id= d.trx_mysql_thread_id
# where c.id= 124 
  order by d.trx_started;

### performance_schema.events_statements_history 
### 则来查看线程最后执行的10条语句
  select thread_id,
         sql_text
  from performance_schema.events_statements_history
# where thread_id = <thread_id>
  order by event_id;

为什么数据库有时候不能定位阻塞源头的sql语句？

对于实际业务场景，线程在执行一个由显示事务（begin）开始，如存储过程或者复杂业务时，有可能它执行完阻塞源头sql后，又会执行其他sql语句，此时，你抓取是这个线程连接最后执行的查询。

如果线程执行的最后一个查询没有足够的信息来确定持有锁的原因，那么需要借助 performance_schema.events_statements_history 来协助，它保存了线程最后执行的10条语句。

下面以 mysql8 为例，简单演示下

先用sysbench搞几个表出来

### RHEL/CentOS系统可以参考如下命令
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
### 完事 sysbench --help 可以看看
### 先生成几个表 自由发挥 
### cd到 /usr/share/sysbench 下 注意8.0密码验证需要修改，先登录mysql创建test库
sysbench --threads=16 --time=60 --report-interval=1 oltp_common.lua --mysql-host=192.168.30.101 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=test --tables=4 --table-size=100000 prepare

环境已准备好

接下来简单测试下

session 1：

### 先执行
begin;
update sbtest1 set k=2 where id<1 ; 
# 执行成功

session 2：

### 再执行
begin; 
insert into sbtest2 select * from sbtest1 where id<5;
# 锁等待...

查看正在活跃的事务和行锁等待情况

mysql> show status like 'innodb_row_lock%';
mysql> select  * from information_schema.innodb_trx;

innodb_row_lock_current_waits：表示当前正在等待行锁的数量

查看哪些事务正在等待哪些事务正在阻塞

可以看到会话125上 insert 事务被会话124阻塞。识别阻塞事务时，由于发出查询的会话124此刻已空闲，则为阻塞查询 blocking_query 置为 NULL 值。

尝试获取锁源 sql

根据阻塞事务的会话124可以看到，它执行的 sql 是 update sbtest1 ，它占有了id=1的行锁，会话125上的事务在等待它释放。信息满足持有锁以及锁等待原因。

但若是此刻在 session 1（也就是会话124）我们随便执行一个查询，这个时候，我们再次查询就会发现锁源 sql 是个无关的查询语句。此时我们没有足够的信息确定持有锁的原因，也无法确定阻塞的源头。所以在 kill 会话时会发生未预知结果。

mysql 对此情况提供了另外一个视图来查看线程最后执行的10条语句。这里我们可以清楚的看到锁的源头。（就10条）

事实上我们通过 mysql 内置的视图 sys.innodb_lock_waits 也可以清楚查看当前锁的持有等待情况，以及它会生成kill语句，以供参考。

对于视图 sys.innodb_lock_waits 而言，mysql5 与 mysql8 比较来看， mysql 对它们的基表做了部分调整。

下面是不同版本下视图 sys.innodb_lock_waits 的创建语句

  #### MySQL5
CREATE VIEW sys.innodb_lock_waits AS
  SELECT ... FROM information_schema.innodb_lock_waits w
  JOIN information_schema.innodb_trx b
    ON b.trx_id = w.blocking_trx_id
  JOIN information_schema.innodb_trx r
    ON r.trx_id = w.requesting_trx_id
  JOIN information_schema.innodb_locks bl
    ON bl.lock_id = w.blocking_lock_id
  JOIN information_schema.innodb_locks rl
    ON rl.lock_id = w.requested_lock_id
  ORDER BY r.trx_wait_starte
  
  #### MySQL8
  CREATE VIEW sys.innodb_lock_waits (...) AS
  SELECT ... FROM performance_schema.data_lock_waits w 
  JOIN information_schema.INNODB_TRX b 
    ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID
  JOIN information_schema.INNODB_TRX r
    ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
  JOIN performance_schema.data_locks bl
    ON bl.ENGINE_LOCK_ID = w.BLOCKING_ENGINE_LOCK_ID
  JOIN performance_schema.data_locks rl
    ON rl.ENGINE_LOCK_ID = w.REQUESTING_ENGINE_LOCK_ID 
  ORDER BY r.trx_wait_started

可以看到有俩张基表做了调整

information_schema.innodb_lock_waits 调整为 PFS.data_lock_waits

information_schema.innodb_locks 调整为 PFS.data_locks

其中需要注意的调整是 performance_schema.data_locks。 后者相比较前者而言，它在记录数据上是有不同的。

innodb_locks：记录了事务已申请但未获得的锁与事务已持有并阻塞其他事务的锁。

data_locks：记录了事务已申请但未获得的锁与事务已持有的锁。

它的不同之处在于一个是记录已持有并阻塞其他事务的锁，一个是已持有的锁都会记录。对此而言，意味着在对大表加锁时，mysql8 不管有没有阻塞其他事务，它都会把每一个锁都记录在表里。在表记录非常大的时候，这个表可能也会瞬时很大，解锁时也会瞬时很小。因此在使用这张表的期间，查询时间也会随之波动，从而影响性能。

假如对一张100w的表加锁，那么 data_locks 里会产生100w+行数据。

metadata lock 锁

简单说下 metadata lock 锁，也称元数据锁。不知大家有无注意到，对于 元数据锁 ，Innodb_row_lock_current_waits 没有计数到？这是因为元数据锁加的是表锁，但它不仅适用于表。

我们经常说读不加锁，怎么就锁住了呢？简单来说

所有的 dml 语句都会在表上加一个 metadata 读锁；

所有的 ddl 语句都会在表上加一个 metadata 写锁；

metadata 读锁和写锁相互阻塞，也就是说同一个表上 dml 和 ddl 相互阻塞。

metadata 写锁和写锁相互阻塞，也就是同时对表做 ddl 相互阻塞。

metadata 读锁和读锁互不影响，dml 和 dml 互不影响。

（此处说的是 metadata 锁，与 innodb 行锁无关。日常中遇到的 dml 之间锁等待是 innodb 行锁引起的。）

知道了元数据锁如何产生，那么如何发现 metadata 锁呢？简单演示下

session 3

### 执行ddl操作，由于 session 1 的存在，会等待...
### session 1
### begin;
### update sbtest1 set k=2 where id<1 ; 
alter table sbtest1 add column d int not null default 0;

最直观的方式就是 show processlist 查看 state 列有 Waiting for table metadata lock 字样。

如果一直持续不做处理，那么后续 dml 语句都会被阻塞，包括元数据锁所涉及到的表上面的查询，然后出现大量等待元数据锁直至连接数耗尽。

那么如何快速找到锁源头，可以参考以下语句。

SELECT
    locked_schema,
    locked_table,
    locked_type,
    waiting_processlist_id,
    waiting_age,
    waiting_query,
    waiting_state,
    blocking_processlist_id,
    blocking_age,
    substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
    sql_kill_blocking_connection
FROM
    (
        SELECT
            b.OWNER_THREAD_ID AS granted_thread_id,
            a.OBJECT_SCHEMA AS locked_schema,
            a.OBJECT_NAME AS locked_table,
            "Metadata Lock" AS locked_type,
            c.PROCESSLIST_ID AS waiting_processlist_id,
            c.PROCESSLIST_TIME AS waiting_age,
            c.PROCESSLIST_INFO AS waiting_query,
            c.PROCESSLIST_STATE AS waiting_state,
            d.PROCESSLIST_ID AS blocking_processlist_id,
            d.PROCESSLIST_TIME AS blocking_age,
            d.PROCESSLIST_INFO AS blocking_query,
            concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
        FROM
            performance_schema.metadata_locks a
        JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
        AND a.OBJECT_NAME = b.OBJECT_NAME
        AND a.lock_status = 'PENDING'
        AND b.lock_status = 'GRANTED'
        AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
        AND a.lock_type = 'EXCLUSIVE'
        JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
        JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
    ) t1,
    (
        SELECT
            thread_id,
            group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
        FROM
           performance_schema.events_statements_history
        GROUP BY thread_id
    ) t2
WHERE
    t1.granted_thread_id = t2.thread_id

可以清楚的看到持有锁和等待锁的语句。

实际上，mysql 也提供了一个类似的视图来解决 metadata lock 问题，视图名称为 sys.schema_table_lock_waits。它显示哪些会话因等待元数据锁而被阻止，以及哪些会话被阻止。

可以看到视图给了俩种处理方式，kill 持有锁的，还生成了 kill 等待锁的，也没毛病。

• 欢迎关注微信公众号，交流探讨。
• 原文地址