MySQL 元数据锁等待及行数据锁等待查询
一、元数据锁等待
1、元数据锁的定义
MDL 全称为 metadata lock，即元数据锁，一般也可称为字典锁。MDL 的主要作用是为了管理数据库对象的并发访问和确保元数据一致性。元数据锁适用对象包含：table、schema、procedures, functions, triggers, scheduled events、tablespaces 。
有以下特点：
- 元数据锁是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请MDL锁。
- DML操作需要metadata读锁，DDL操作需要metadata写锁，加锁过程是系统自动控制，无法直接干预。
- 读锁和写锁之间相互阻塞，写锁和写锁之间互相阻塞，读锁和读锁之间不会产生阻塞（因为DML之间的阻塞是通过innodb引擎的行锁来进行的）。
- DDL 的写锁请求优先级高于 DML的读锁。
- MDL锁保护的是事务级别的，只有等到事务结束后才会释放。
- 同时操作多个表时，按出现的先后顺序逐个表去获取元数据锁。
- 单个DML语句，在语句执行完后会立即释放元数据锁。
- DDL 语句、LOCK TABLES 和其他类似语句按名称顺序获取锁，对于隐式使用的表(例如外键关系中也必须锁定的表)可能会以不同的顺序获取锁。
加入元数据锁的目的：
- 事务隔离问题：比如在RR隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求。
- 数据复制问题：比如会话A执行了多条更新语句期间，会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update，这样会导致复制出现错误。
2、快速查看是否有元数据锁等待产生
show processlist或select * from information_schema.processlist，查看结果集中STATE列是否有Waiting for table metadata lock显示，有则为产生了元数据锁等等。
[5.7.37-log]>select * from information_schema.processlist; +----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+ | 10 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist | | 5 | root | localhost | performance_schema | Sleep | 164 | | NULL | | 9 | root | localhost | test | Query | 22 | Waiting for table metadata lock | alter table t1 add addr varchar(100) | | 8 | root | localhost | test | Sleep | 24 | | NULL | +----+------+-----------+--------------------+---------+------+---------------------------------+----------------------------------------------+ 4 rows in set (0.00 sec)
3、开启并监控元数据锁信息
1）永久生效，在配置文件的[mysqld]节点下，加入开启参数
[mysqld] performance_schema_instrument='wait/lock/metadata/sql/mdl=ON'
2) 临时生效，数据库重启后，变回默认值
update performance_schema.setup_instruments set ENABLED='Yes',TIMED='YES' where name='wait/lock/metadata/sql/mdl';
3）监控元数据锁信息：都记录在performance_schema.metadata_locks表中
[5.7.37-log]>select * from performance_schema.metadata_locks; +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ | TABLE | performance_schema | metadata_locks | 140398722883648 | SHARED_READ | TRANSACTION | GRANTED | | 28 | 8 | +-------------+--------------------+----------------+-----------------------+-------------+---------------+-------------+--------+-----------------+----------------+ 1 row in set (0.00 sec)
4、模拟元数据锁等待
--session 1 [5.7.37-log]>begin; Query OK, 0 rows affected (0.00 sec) [5.7.37-log]>select * from t1; +------+------+ | id | name | +------+------+ | 1 | NULL | | 4 | NULL | | 5 | NULL | +------+------+ 3 rows in set (0.00 sec) --session 2 [5.7.37-log]>alter table t1 add addr varchar(100); --session 3 # 查看元数据锁等待 select a.object_schema locked_schema, a.object_name locked_table, "metadata lock" locked_type, c.processlist_id waiting_processlist_id, c.processlist_time waiting_time, c.processlist_info waiting_query, c.processlist_state waiting_state, d.processlist_id blocking_processlist_id, d.processlist_time blocking_time, e.sql_text blocking_query, concat('kill ',d.processlist_id, ';') 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 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 join performance_schema.events_statements_current e on b.owner_thread_id = e.thread_id\G *************************** 1. row *************************** locked_schema: test locked_table: t1 locked_type: metadata lock waiting_processlist_id: 5 waiting_time: 13 waiting_query: alter table t1 add addr varchar(100) waiting_state: Waiting for table metadata lock blocking_processlist_id: 4 blocking_time: 42 blocking_query: select * from t1 sql_kill_blocking_connection: kill 4 1 row in set (0.00 sec)
二、行锁等待
有锁阻塞时，可以通过show engine innodb status命令，及表information_schema.innodb_lock_waits进行查看堵塞信息，也可以通过innodb_lock_waits表结合其他表，查看阻塞与被阻塞的进程信息
show engine innodb status：
[5.7.37-log]>show engine innodb status\G ------------ TRANSACTIONS ------------ Trx id counter 641553 Purge done for trx's n:o < 641542 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421875474013808, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 641552, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 5, OS thread handle 140399866201856, query id 75 localhost root updating update t1 set name='abcd' where id=1 ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 148 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`t1` trx id 641552 lock_mode X waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000500; asc ;; 1: len 6; hex 00000009b52b; asc +;; 2: len 7; hex c7000002530110; asc S ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; ------------------ ---TRANSACTION 641542, ACTIVE 1491 sec 2 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id 4, OS thread handle 140399866472192, query id 30 localhost root
information_schema.innodb_lock_waits：
[5.7.37-log]>select * from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 641553 | 641553:148:3:3 | 641542 | 641542:148:3:3 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
查看阻塞信息：
select wt.thread_id waiting_thread_id, wt.processlist_id waiting_processlist_id, wt.processlist_time waiting_time, wt.processlist_info waiting_query, bt.thread_id blocking_thread_id, bt.processlist_id blocking_processlist_id, bt.processlist_time blocking_time, c.sql_text blocking_query, concat('kill ',bt.processlist_id, ';') sql_kill_blocking_connection from information_schema.innodb_lock_waits l join information_schema.innodb_trx b on b.trx_id = l.blocking_trx_id join information_schema.innodb_trx w on w.trx_id = l.requesting_trx_id join performance_schema.threads wt on w.trx_mysql_thread_id=wt.processlist_id join performance_schema.threads bt on b.trx_mysql_thread_id=bt.processlist_id join performance_schema.events_statements_current c on bt.thread_id=c.thread_id\G *************************** 1. row *************************** waiting_thread_id: 30 waiting_processlist_id: 5 waiting_time: 27 waiting_query: update t1 set name='abcd' where id=1 blocking_thread_id: 29 blocking_processlist_id: 4 blocking_time: 1681 blocking_query: select * from t1 for update sql_kill_blocking_connection: kill 4; 1 row in set, 1 warning (0.00 sec)
查看引起堵塞的事务SQL
select wt.thread_id waiting_thread_id, wt.processlist_id waiting_processlist_id, wt.processlist_time waiting_time, wt.processlist_info waiting_query, bt.thread_id blocking_thread_id, bt.processlist_id blocking_processlist_id, bt.processlist_time blocking_time, c.sql_text blocking_query, concat('kill ', bt.processlist_id, ';') sql_kill_blocking_connection from information_schema.innodb_lock_waits l join information_schema.innodb_trx b on b.trx_id = l.blocking_trx_id join information_schema.innodb_trx w on w.trx_id = l.requesting_trx_id join performance_schema.threads wt on w.trx_mysql_thread_id = wt.processlist_id join performance_schema.threads bt on b.trx_mysql_thread_id = bt.processlist_id join ( select a.thread_id,group_concat(a.sql_text order by a.event_id SEPARATOR ';\n') sql_text from ( select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_current union select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_history ) a join ( select thread_id, max(event_id) max_event_id, max(case when event_name = 'statement/sql/begin' or (event_name = 'statement/sql/set_option' and sql_text like '%autocommit%=%0%') then event_id else 0 end) max_begin_id, max(case when event_name = 'statement/sql/rollback' then event_id else 0 end) max_rollback_id, max(case when event_name = 'statement/sql/commit' then event_id else 0 end) max_commit_id from ( select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_current union select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_history ) tmp group by thread_id ) b on a.thread_id = b.thread_id where a.event_id = b.max_event_id or ( a.event_id >= max_begin_id and max_begin_id > greatest(max_rollback_id, max_commit_id) ) group by a.thread_id ) c on bt.thread_id = c.thread_id \G
三、未提交事务
select a.trx_id, a.trx_state, a.trx_started, b.processlist_time, b.thread_id, b.processlist_id, b.processlist_user, b.processlist_host, b.processlist_db, c.sql_text, concat('kill ',b.processlist_id, ';') sql_kill_connection from information_schema.innodb_trx a join performance_schema.threads b on a.trx_mysql_thread_id=b.processlist_id join performance_schema.events_statements_current c on b.thread_id=c.thread_id\G *************************** 1. row *************************** trx_id: 641560 trx_state: RUNNING trx_started: 2022-10-30 10:49:22 processlist_time: 74 thread_id: 29 processlist_id: 4 processlist_user: root processlist_host: localhost processlist_db: test sql_text: select * from t1 for update sql_kill_connection: kill 4; *************************** 2. row *************************** trx_id: 641561 trx_state: LOCK WAIT trx_started: 2022-10-30 10:50:28 processlist_time: 8 thread_id: 30 processlist_id: 5 processlist_user: root processlist_host: localhost processlist_db: test sql_text: update t1 set name='abcd' where id=1 sql_kill_connection: kill 5; 2 rows in set (0.00 sec)
查看完整未提交的事务SQL
select a.trx_id, a.trx_state, a.trx_started, b.processlist_time, b.thread_id, b.processlist_id, b.processlist_user, b.processlist_host, b.processlist_db, c.sql_text, concat('kill ',b.processlist_id, ';') sql_kill_connection from information_schema.innodb_trx a join performance_schema.threads b on a.trx_mysql_thread_id=b.processlist_id join ( select a.thread_id,group_concat(a.sql_text order by a.event_id SEPARATOR ';\n') sql_text from ( select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_current union select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_history ) a join ( select thread_id, max(event_id) max_event_id, max(case when event_name = 'statement/sql/begin' or (event_name = 'statement/sql/set_option' and sql_text like '%autocommit%=%0%') then event_id else 0 end) max_begin_id, max(case when event_name = 'statement/sql/rollback' then event_id else 0 end) max_rollback_id, max(case when event_name = 'statement/sql/commit' then event_id else 0 end) max_commit_id from ( select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_current union select thread_id,event_id,event_name,sql_text from performance_schema.events_statements_history ) tmp group by thread_id ) b on a.thread_id = b.thread_id where a.event_id = b.max_event_id or ( a.event_id >= max_begin_id and max_begin_id > greatest(max_rollback_id, max_commit_id) ) group by a.thread_id ) c on b.thread_id=c.thread_id \G
浙公网安备 33010602011771号