MySQL中找出谁持有MDL锁
一、MDL锁的介绍
MySQL 5.7版本之前并没有提供一个方便的途径来查看MDL锁，github上有一名为mysql-plugin-mdl-info的项目，通过插件的方式来查看，于是在MySQL 5.7中的performance_schea库下新增了一张表metadata_locks，用其来查看MDL锁那是相当的方便:
不过默认PS并没有打开此功能，需要手工将wait/lock/metadata/sql/mdl监控给打开:
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl';
- 1.
- 2.
- 3.
- 4.
目前MDL有如下锁模式：
锁模式 对应SQL
MDL_INTENTION_EXCLUSIVE GLOBAL对象、SCHEMA对象操作会加此锁
MDL_SHARED FLUSH TABLES with READ LOCK
MDL_SHARED_HIGH_PRIO 仅对MyISAM存储引擎有效
MDL_SHARED_READ SELECT查询
MDL_SHARED_WRITE DML语句
MDL_SHARED_WRITE_LOW_PRIO 仅对MyISAM存储引擎有效
MDL_SHARED_UPGRADABLE ALTER TABLE
MDL_SHARED_READ_ONLY LOCK xxx READ
MDL_SHARED_NO_WRITE FLUSH TABLES xxx,yyy,zzz READ
MDL_SHARED_NO_READ_WRITE FLUSH TABLE xxx WRITE
MDL_EXCLUSIVE ALTER TABLE xxx PARTITION BY …
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
root@tidb06 08:33: [test001]> select * from performance_schema.setup_instruments WHERE NAME ='wait/lock/metadata/sql/mdl';
+----------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+----------------------------+---------+-------+
| wait/lock/metadata/sql/mdl | YES | YES |
+----------------------------+---------+-------+
1 row in set (0.00 sec)
root@tidb06 08:44: [test001]> select * from performance_schema.setup_consumers where NAME ='global_instrumentation';
+------------------------+---------+
| NAME | ENABLED |
+------------------------+---------+
| global_instrumentation | YES |
+------------------------+---------+
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
二、模拟MDL锁
演示环境:mysql5.7.22
为了演示 下面参数设置时间如下:
innodb_lock_wait_timeout=600
interactive_timeout = 600
wait_timeout =600
- 1.
- 2.
- 3.
创建测试表和插入测试数据:
CREATE TABLE `test001` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`username` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`create_time` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
insert into test001(username,password,create_time)value('小花','abc123',now());
insert into test001(username,password,create_time)value('王五','ccc123',now());
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
会话1 开启事务更新1条sql:
'tidb03' root@localhost 09:04:56 test001>begin;
Query OK, 0 rows affected (0.00 sec)
'tidb03' root@localhost 09:05:03 test001>update test001 set username='zhangsan' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
会话2 更新同一个记录: 产生行锁等待
'tidb03' root@localhost 12:40:02 test001>update test001 set username='王五' where id=2;
- 1.
会话3 给test001表添加索引 : 产生DML表锁等待
'tidb03' root@localhost 12:42:36 test001>alter table test001 add index idx_username(username);
- 1.
- 2.
会话4 给test001表删除id=2的记录数:同样产生MDL锁等待
'tidb03' root@localhost 12:42:42 (none)>show full processlist;
+--------+------+-----------------+---------+---------+------+---------------------------------+------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+---------------------------------+------------------------------------------------------+
| 134004 | root | localhost:40418 | test001 | Sleep | 111 | | NULL |
| 134122 | root | localhost:40806 | test001 | Query | 89 | updating | update test001 set username='王五' where id=2 |
| 134336 | root | localhost:41542 | test001 | Query | 14 | Waiting for table metadata lock | alter table test001 add index idx_username(username) |
| 134384 | root | localhost:41716 | NULL | Query | 0 | starting | show full processlist |
+--------+------+-----------------+---------+---------+------+---------------------------------+------------------------------------------------------+
4 rows in set (0.00 sec)
'tidb03' root@localhost 12:42:51 (none)>delete from test001.test001 where id=2;
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
会话5： 查看 出现2个metadata lock
'tidb03' root@localhost 12:47:09 (none)>
'tidb03' root@localhost 13:32:12 (none)>show full processlist;
+--------+------+-----------------+---------+---------+------+---------------------------------+-------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------------+---------+---------+------+---------------------------------+-------------------------------------------------+
| 136439 | root | localhost:48814 | test001 | Sleep | 55 | | NULL |
| 136461 | root | localhost:48886 | test001 | Query | 49 | updating | update test001 set username='王五' where id=2 |
| 136468 | root | localhost:48910 | test001 | Query | 24 | Waiting for table metadata lock | alter table test001 drop index idx_username |
| 136474 | root | localhost:48924 | test001 | Query | 14 | Waiting for table metadata lock | delete from test001.test001 where id=2 |
| 136528 | root | localhost:49112 | NULL | Query | 0 | starting | show full processlist |
+--------+------+-----------------+---------+---------+------+---------------------------------+-------------------------------------------------+
5 rows in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
通过下面的语句可以知道哪些线程持有了MDL锁:
select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id())\G
'tidb03' root@localhost 13:32:14 (none)>select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id())\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120825061232
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136464
OWNER_EVENT_ID: 11
*************************** 2. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140121026476464
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 136486
OWNER_EVENT_ID: 7
*************************** 3. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140121026529536
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136486
OWNER_EVENT_ID: 7
*************************** 4. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140120892297488
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5533
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 5. row ***************************
OBJECT_TYPE: SCHEMA
OBJECT_SCHEMA: test001
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140120892297584
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5518
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 6. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120892219008
LOCK_TYPE: SHARED_UPGRADABLE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 7. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120892154656
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: mdl.cc:3919
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 8. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140121228648400
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 136499
OWNER_EVENT_ID: 9
*************************** 9. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140121227688160
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136499
OWNER_EVENT_ID: 9
9 rows in set (0.00 sec)
###########################################
###########################################
'tidb03' root@localhost 13:32:42 (none)>select * from performance_schema.metadata_locks where OWNER_THREAD_ID!=sys.ps_thread_id(connection_id()) and OWNER_THREAD_ID not in (136468,136474)\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120825061232
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136464
OWNER_EVENT_ID: 11
*************************** 2. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140121026476464
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 136486
OWNER_EVENT_ID: 7
*************************** 3. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140121026529536
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136486
OWNER_EVENT_ID: 7
*************************** 4. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140120892297488
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5533
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 5. row ***************************
OBJECT_TYPE: SCHEMA
OBJECT_SCHEMA: test001
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140120892297584
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:5518
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 6. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120892219008
LOCK_TYPE: SHARED_UPGRADABLE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 7. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140120892154656
LOCK_TYPE: EXCLUSIVE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: mdl.cc:3919
OWNER_THREAD_ID: 136493
OWNER_EVENT_ID: 11
*************************** 8. row ***************************
OBJECT_TYPE: GLOBAL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140121228648400
LOCK_TYPE: INTENTION_EXCLUSIVE
LOCK_DURATION: STATEMENT
LOCK_STATUS: GRANTED
SOURCE: sql_base.cc:3190
OWNER_THREAD_ID: 136499
OWNER_EVENT_ID: 9
*************************** 9. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test001
OBJECT_NAME: test001
OBJECT_INSTANCE_BEGIN: 140121227688160
LOCK_TYPE: SHARED_WRITE
LOCK_DURATION: TRANSACTION
LOCK_STATUS: PENDING
SOURCE: sql_parse.cc:6020
OWNER_THREAD_ID: 136499
OWNER_EVENT_ID: 9
9 rows in set (0.00 sec)
#######但是不能看到线程id到底执行了什么语句#######
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
- 87.
- 88.
- 89.
- 90.
- 91.
- 92.
- 93.
- 94.
- 95.
- 96.
- 97.
- 98.
- 99.
- 100.
- 101.
- 102.
- 103.
- 104.
- 105.
- 106.
- 107.
- 108.
- 109.
- 110.
- 111.
- 112.
- 113.
- 114.
- 115.
- 116.
- 117.
- 118.
- 119.
- 120.
- 121.
- 122.
- 123.
- 124.
- 125.
- 126.
- 127.
- 128.
- 129.
- 130.
- 131.
- 132.
- 133.
- 134.
- 135.
- 136.
- 137.
- 138.
- 139.
- 140.
- 141.
- 142.
- 143.
- 144.
- 145.
- 146.
- 147.
- 148.
- 149.
- 150.
- 151.
- 152.
- 153.
- 154.
- 155.
- 156.
- 157.
- 158.
- 159.
- 160.
- 161.
- 162.
- 163.
- 164.
- 165.
- 166.
- 167.
- 168.
- 169.
- 170.
- 171.
- 172.
- 173.
- 174.
- 175.
- 176.
- 177.
- 178.
- 179.
- 180.
- 181.
- 182.
- 183.
- 184.
- 185.
- 186.
- 187.
- 188.
- 189.
- 190.
- 191.
- 192.
- 193.
- 194.
- 195.
- 196.
- 197.
- 198.
- 199.
- 200.
- 201.
- 202.
- 203.
- 204.
- 205.
- 206.
- 207.
- 208.
- 209.
查看关于故障库test001下的 MySQL的线程id和processlist id的对应关系(方便知道线程id和processlist id的对应关系):
mysql -e "select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_INFO from performance_schema.threads where PROCESSLIST_DB='test001';"
- 1.
查看故障库test001中存在的当前的mysql的线程id和对应的 processlist id,发现第一个线程已经是sleep状态,看不到它具体执行了什么命令:
[root@tidb03 ~]# mysql -e "select THREAD_ID,PROCESSLIST_ID,PROCESSLIST_USER,PROCESSLIST_HOST,PROCESSLIST_COMMAND,PROCESSLIST_INFO from performance_schema.threads where PROCESSLIST_DB='test001';"
+-----------+----------------+------------------+------------------+---------------------+-------------------------------------------------+
| THREAD_ID | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESSLIST_COMMAND | PROCESSLIST_INFO |
+-----------+----------------+------------------+------------------+---------------------+-------------------------------------------------+
| 136464 | 136439 | root | localhost | Sleep | NULL |
| 136486 | 136461 | root | localhost | Query | update test001 set username='王五' where id=2 |
| 136493 | 136468 | root | localhost | Query | alter table test001 drop index idx_username |
| 136499 | 136474 | root | localhost | Query | delete from test001.test001 where id=2 |
+-----------+----------------+------------------+------------------+---------------------+-------------------------------------------------+
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
需要查看事务表，确认 trx_mysql_thread_id: 136439 线程是否存在一个未提交的事务:
发现确实存在一个未提交的事务,但是看不到具体的sql.（我们当然可以KILL 136439 ，让后面的sql继续执行下去，但是拿不到136439线程到底在执行什么sql，这样无法让研发进行优化）
'tidb03' root@localhost 13:36:43 (none)>select * from information_schema.innodb_trx where trx_mysql_thread_id=136439\G
*************************** 1. row ***************************
trx_id: 47819044
trx_state: RUNNING
trx_started: 2021-09-19 13:31:19
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 136439
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
查看当前正在执行的sql:
select * from performance_schema.events_statements_current;
借助performance_schema.events_statements_current 表查看某个线程正在执行的sql或者最后一次执行完成的语句时间信息
（这里的信息并不是完全的可靠，因为此表只记录每个线程当前正在执行和最近一次执行完成的语句信息一旦这个线程执行新的语句，信息就会被覆盖）
下面的sql CURRENT_SCHEMA='test001' 指的是故障表所在的库; THREAD_ID= 指的是MySQL的 processlist id
select EVENT_NAME,TIMER_START,TIMER_END,SQL_TEXT from performance_schema.events_statements_current where CURRENT_SCHEMA='test001' and THREAD_ID=''\G
- 1.
'tidb03' root@localhost 13:36:57 (none)>select EVENT_NAME,TIMER_START,TIMER_END,SQL_TEXT from performance_schema.events_statements_current where CURRENT_SCHEMA='test001' and THREAD_ID='136464'\G
*************************** 1. row ***************************
EVENT_NAME: statement/sql/update
TIMER_START: 247274896299307000
TIMER_END: 247274896710996000
SQL_TEXT: update test001 set username='zhangsan' where id=2
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
到此处最终定位到了 会话1处的 事务未提交的sql:
update test001 set username='zhangsan' where id=2
查看此时关于 136464 id的全部信息如下:
'tidb03' root@localhost 13:37:35 (none)>select * from performance_schema.events_statements_current where CURRENT_SCHEMA='test001' and THREAD_ID='136464'\G
*************************** 1. row ***************************
THREAD_ID: 136464
EVENT_ID: 10
END_EVENT_ID: 10
EVENT_NAME: statement/sql/update
SOURCE: socket_connection.cc:101
TIMER_START: 247274896299307000
TIMER_END: 247274896710996000
TIMER_WAIT: 411689000
LOCK_TIME: 138000000
SQL_TEXT: update test001 set username='zhangsan' where id=2
DIGEST: d29647dac864e192341acb8c50b0098c
DIGEST_TEXT: UPDATE `test001` SET `username` = ? WHERE `id` = ?
CURRENT_SCHEMA: test001
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: 00000
MESSAGE_TEXT: Rows matched: 1 Changed: 1 Warnings: 0
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 1
ROWS_SENT: 0
ROWS_EXAMINED: 1
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
在会话1 对 刚才未提交事务的sql update test001 set username='zhangsan' where id=2 发起commit;
'tidb03' root@localhost 13:31:18 test001>update test001 set username='zhangsan' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
'tidb03' root@localhost 13:31:19 test001>
'tidb03' root@localhost 13:38:38 test001>commit;
Query OK, 0 rows affected (0.01 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
此时后面被阻塞的sql完成了正常的提交。
此时再查看THREAD_ID='136464' 最新执行过的sql动作如下:
SQL_TEXT: commit
'tidb03' root@localhost 13:38:33 (none)>select * from performance_schema.events_statements_current where CURRENT_SCHEMA='test001' and THREAD_ID='136464'\G
*************************** 1. row ***************************
THREAD_ID: 136464
EVENT_ID: 11
END_EVENT_ID: 11
EVENT_NAME: statement/sql/commit
SOURCE: socket_connection.cc:101
TIMER_START: 247717969501165000
TIMER_END: 247717976326700000
TIMER_WAIT: 6825535000
LOCK_TIME: 0
SQL_TEXT: commit
DIGEST: 98bf18ef8a1606965a0f2ff85fa992a3
DIGEST_TEXT: COMMIT
CURRENT_SCHEMA: test001
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: 00000
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
1 row in set (0.00 sec)
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.
- 81.
- 82.
- 83.
- 84.
- 85.
- 86.
到此处整个MDL锁排查完成。
MDL锁简单说明参考文章:
https://www.cnblogs.com/zengkefu/p/5690385.html
