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)
首先开启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)
但是以上信息无法查看无法轻松地从看到正在等待MDL的线程回溯到哪个线程持有该锁。
需要查看另外一个视图获取信息：
(root@localhost 11:23:23) [performance_schema](41)> select * from sys.schema_table_lock_waits where blocking_pid != waiting_pid \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是最方便的。