MySQL锁表你用对了吗？_vlambda技术博客

vlambda
2022-04-10

MySQL锁表你用对了吗？

1. 描述
2. 排查过程

2.1 查看当前锁表状态
2.2 查看元数据锁情况
2.3 查看对应线程详细情况
2.4 定位
2.5 锁释放
2.6 Checker执行流程

3. 原因
4. 解决方法

很明显，我没用对，不然就不会有这个事儿了

1. 描述

在测试增量迁移工具时，往源库中执行DML操作（Insert、Delete、Update），操作有很大几率阻塞，需要等待将近1分钟才可以执行成功。查看PROCESSLIST可以看到操作阻塞的原因是因为在等待获取metadata lock，如下图所示：

2. 排查过程

2.1 查看当前锁表状态

通过执行show OPEN TABLES where In_use > 0;，我们可以看到当前checker、mig3表均被锁定

mysql> show OPEN TABLES where In_use > 0;
+--------------------------+---------+--------+-------------+
| Database                 | Table   | In_use | Name_locked |
+--------------------------+---------+--------+-------------+
| _tec_db_transfer_checker | checker |      1 |           0 |
| g                        | mig3    |      1 |           0 |
+--------------------------+---------+--------+-------------+

2.2 查看元数据锁情况

找到object_type为TABLE类型的行，可以看到表mig3的lock_type为SHARED_READ_ONLY，并且持有锁的线程id为21851。线程id21404的lock_status处于PENDING的状态，正是因为线程id21851未将锁释放导致的

MySQL锁表你用对了吗？

lock_status为 PENDING代表线程在等待锁，GRANTED则代表线程持有锁

2.3 查看对应线程详细情况

通过查看对应线程的详细情况，可以看到目前持有锁的线程状态处于Sleep中

mysql> select * from performance_schema.threads where thread_id IN (21851,21404)\G;
*************************** 1. row ***************************
          THREAD_ID: 21404
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 21370
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: 10.17.6.64
     PROCESSLIST_DB: g
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 111
  PROCESSLIST_STATE: Waiting for table metadata lock
   PROCESSLIST_INFO: insert into mig3 values('78888888s8', 111)
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 65
     RESOURCE_GROUP: USR_default
*************************** 2. row ***************************
          THREAD_ID: 21851
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 21817
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: 10.17.6.64
     PROCESSLIST_DB: g
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 51
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 60
     RESOURCE_GROUP: USR_default
2 rows in set (0.00 sec)

2.4 定位

Checker模块的流程能够正常的执行，且线程处于Sleep状态本应该是正常的现象（连接池）。但现在却会出现规律的 i/o 超时和 DML操作阻塞的问题。因此怀疑锁是否未正常的释放，于是在Checker模块中输出当前的会话ID，发现同一次Checksum流程会出现不同的会话ID，更加确定是锁未正常的释放导致的

2.5 锁释放

锁释放主要分为两种类型，显式释放和隐式释放。其中Checker模块使用到的UNLOCK TABLES便属于显示释放，于是将目光定位到隐式释放上，看看是否有什么细节被我们忽略了

会话开启事务

如果当前会话已经持有锁，并且开始事务，则会隐式执行UNLOCK TABLES，释放现有锁

会话重复LOCK

会话重复LOCK会隐式的将之前的锁释放，再重新加锁

会话连接结束/终止

会话的结束或者终止，都会隐式释放会话获取的锁

这也是我们平常比较常用的解决死锁的方式，直接kill掉对应的连接线程，但治标不治本，不能知道是什么原因导致了死锁

2.6 Checker执行流程

几种隐式释放锁的条件都提到了相同的一点：会话，再结合Checker的执行流程，我们可以得到下图流程：

图中绿色环节（SessionB）在Checksum前需要锁表，但由于蓝色环节（SessionA）未成功的将释放锁，导致绿色环节阻塞，直到橙色环节（SessionA）成功释放锁

SessionA锁表 —> Checksum —> SessionB解锁（无效释放锁）—> SessionB/C/? 锁表（阻塞）—> 超时报错 —> SessionA锁表（隐式释放锁）—> Checksum —> SessionA解锁（显示释放锁）

这也就解释了为什么会报出i/o timeout以及DML操作要阻塞一段时间才可以执行的错误：皆是因为MDL锁未正常释放导致的

3. 原因

综上所述，造成DML阻塞的根本原因如下：

使用了连接池，Checker模块中会存在使用多个会话连接的情况；
获取锁和释放锁的操作不是在同一个会话中完成的，导致锁不能正常释放，直至Checker的下一次检查流程；

A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

4. 解决方法

需要确保LOCK和UNLOCK在同一个会话中执行。有如下几种方案可以保持同一个连接会话：

连接配置的 MaxOpen、MaxIdle同时设置为1
将多条SQL操作一次性提交执行（需要在DSN中设置 multiStatements=true）

vlambda博客
学习文章列表