MySQL SELECT阻塞表的DDL操作

 

MySQL SELECT阻塞表的DDL操作

 

如标题所示，最近发现了这么个奇葩的现象。

版本：8.0.29

当然，这边数据库服务器默认的自动提交被关闭了，跟Oracle一样DML操作都需要手工commit。

(root@localhost 10:48:21) [performance_schema](44)> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

防爬虫：https://www.cnblogs.com/PiscesCanon/p/17302790.html

 

首先开启3个会话，connID分别是41,44,45。

其中，conID=41为观察者，先查看表zkm.test的元数据锁情况，如下：

(root@localhost 10:48:57) [performance_schema](41)> select * from performance_schema.metadata_locks where object_name='test';
Empty set (0.00 sec)

 

connID=44执行：

(root@localhost 10:50:00) [performance_schema](44)> select count(*) from  zkm.test;
+----------+
| count(*) |
+----------+
|  1048577 |
+----------+
1 row in set (0.10 sec)

 

connID=45执行：

(root@localhost 10:48:55) [performance_schema](45)> alter table zkm.test modify id int not null;
...等待...

 

connID=41(观察者)查看线程列表和MDL锁情况：

(root@localhost 11:16:01) [performance_schema](41)> show processlist;
+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+---------------------------------------------+
| Id | User            | Host      | db                 | Command | Time  | State                           | Info                                        |
+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+---------------------------------------------+
|  5 | event_scheduler | localhost | NULL               | Daemon  | 42875 | Waiting on empty queue          | NULL                                        |
| 41 | root            | localhost | performance_schema | Query   |     0 | init                            | show processlist                            |
| 44 | root            | localhost | performance_schema | Sleep   |     2 |                                 | NULL                                        |
| 45 | root            | localhost | performance_schema | Query   |  1377 | Waiting for table metadata lock | alter table zkm.test modify id int not null |
+----+-----------------+-----------+--------------------+---------+-------+---------------------------------+---------------------------------------------+
4 rows in set (0.00 sec)

(root@localhost 11:16:11) [performance_schema](41)> select ml.*,td.processlist_id CONN_ID from performance_schema.metadata_locks ml,performance_schema.threads td where td.thread_id=ml.OWNER_THREAD_ID and ml.object_name='test';
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+---------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID | CONN_ID |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+---------+
| TABLE       | zkm           | test        | NULL        |       139929298400976 | SHARED_READ       | TRANSACTION   | GRANTED     | sql_parse.cc:5981 |             204 |            120 |      44 |
| TABLE       | zkm           | test        | NULL        |       139929365643312 | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     | sql_parse.cc:5981 |             205 |            117 |      45 |
| TABLE       | zkm           | test        | NULL        |       139929372238672 | EXCLUSIVE         | TRANSACTION   | PENDING     | mdl.cc:3753       |             205 |            118 |      45 |
+-------------+---------------+-------------+-------------+-----------------------+-------------------+---------------+-------------+-------------------+-----------------+----------------+---------+
3 rows in set (0.00 sec)

 

可以看到，在connID=45的会话正在等待"Waiting for table metadata lock"，视图performance_schema.metadata_locks中倒数第一行lock_status=‘PENDING’表示处于等待状态。

更详细的信息，参考如下：

(root@localhost 11:19:43) [performance_schema](41)> SELECT ps.*,lock_summary.lock_summary
    -> FROM sys.processlist ps
    -> INNER JOIN(
    -> SELECT owner_thread_id, group_concat( DISTINCT concat(mdl.lock_status, ' ', mdl.lock_type, ' on ',IF(mdl.object_type='USER LEVEL LOCK', Concat(mdl.object_name, ' (user lock)'), Concat(mdl.object_schema, '.', mdl.object_name))) ORDER BY mdl.object_type ASC, mdl.lock_status ASC, mdl.lock_type ASC separator '\n' ) AS lock_summary
    -> FROM performance_schema.metadata_locks mdl
    -> GROUP BY owner_thread_id) lock_summary
    -> ON (ps.thd_id=lock_summary.owner_thread_id)
    -> WHERE ps.conn_id=45 \G
*************************** 1. row ***************************
                thd_id: 205
               conn_id: 45
                  user: root@localhost
                    db: performance_schema
               command: Query
                 state: Waiting for table metadata lock
                  time: 1667
     current_statement: alter table zkm.test modify id int not null
      execution_engine: PRIMARY
     statement_latency: 27.79 min
              progress: NULL
          lock_latency: 17.00 us
           cpu_latency:   0 ps
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 80.86 KiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: 27.79 min
             trx_state: ACTIVE
        trx_autocommit: NO
                   pid: 22335
          program_name: mysql
          lock_summary: GRANTED EXCLUSIVE on zkm.#sql-5073_2d
GRANTED SHARED_UPGRADABLE on zkm.test
PENDING EXCLUSIVE on zkm.test
1 row in set (0.14 sec)

综上可知，connID=44执行"select"操作，成功在表zkm.test上获取了SHARED_READ锁。

而后，connID=45执行"alter"操作，先成功在表zkm.test上获取SHARED_UPGRADABLE锁，接着继续获取EXCLUSIVE锁时候进去等待状态。

因此可知道，SHARED_READ和SHARED_UPGRADABLE互相兼容，而SHARED_READ会阻塞EXCLUSIVE。

 

但是以上信息无法查看无法轻松地从看到正在等待MDL的线程回溯到哪个线程持有该锁。

需要查看另外一个视图sys.schema_table_lock_waits获取信息，视图显示哪些会话在等待MDL锁，以及什么阻止了它们：

(root@localhost 11:23:23) [performance_schema](41)> select * from sys.schema_table_lock_waits where blocking_lock_type <> 'SHARED_UPGRADABLE' \G
*************************** 1. row ***************************
               object_schema: zkm
                 object_name: test
           waiting_thread_id: 205
                 waiting_pid: 45
             waiting_account: root@localhost
           waiting_lock_type: EXCLUSIVE
       waiting_lock_duration: TRANSACTION
               waiting_query: alter table zkm.test modify id int not null
          waiting_query_secs: 1811
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 204
                blocking_pid: 44
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 44
sql_kill_blocking_connection: KILL 44
1 row in set (0.01 sec)

 

其中，waiting_pid=45表示当前被阻塞导致等待的会话，而blocking_pid=44则是阻塞源头。

 

因此，是要看connID=44会话为何持有锁不放，根据原因确定如何释放锁。

当然，直接kill是最方便的。