# 会话1 事务中执行DML操作
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student_tb (stu_id,stu_name) values (1009,'xin');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | create_time | update_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | from1 | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 2 | 1002 | dfsfd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 3 | 1003 | fdgfg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 4 | 1004 | sdfsdf | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 5 | 1005 | dsfsdg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 6 | 1006 | fgd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 7 | 1007 | fgds | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 8 | 1008 | dgfsa | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 |
| 9 | 1009 | xin | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 |
+--------------+--------+----------+---------------------+---------------------+
# 会话2 对该表加字段 执行DDL操作 发现DDL挂起
mysql> alter table student_tb add stu_age int after stu_name;
# 会话3 查询所有会话 发现发生MDL锁
mysql> show processlist;
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
| 31 | root | localhost | testdb | Sleep | 125 | | NULL |
| 32 | root | localhost | testdb | Query | 7 | Waiting for table metadata lock | alter table student_tb add stu_age int after stu_name |
| 33 | root | localhost | testdb | Query | 0 | starting | show processlist |
+----+------+-----------+--------+---------+------+---------------------------------+-------------------------------------------------------+
# 会话3 查看metadata_locks表记录 发现student_tb表有MDL锁冲突
mysql> 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 | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 20 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+
# 会话3 联合其他系统表 查找出会话ID
mysql> select m.*,t.PROCESSLIST_ID from performance_schema.metadata_locks m left join performance_schema.threads t on m.owner_thread_id=t.thread_id;
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
| TABLE | testdb | student_tb | 94189250717664 | SHARED_WRITE | TRANSACTION | GRANTED | | 56 | 34 | 31 |
| GLOBAL | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | | 57 | 18 | 32 |
| SCHEMA | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | | 57 | 18 | 32 |
| TABLE | testdb | student_tb | 139764477697696 | EXCLUSIVE | TRANSACTION | PENDING | | 57 | 18 | 32 |
| TABLE | performance_schema | metadata_locks | 139764544135120 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
| TABLE | performance_schema | threads | 139764549217280 | SHARED_READ | TRANSACTION | GRANTED | | 58 | 22 | 33 |
+-------------+--------------------+----------------+-----------------------+---------------------+---------------+-------------+--------+-----------------+----------------+----------------+
# 结果解读:从上面结果明显可以看出会话31持有student_tb表的SHARED_WRITE锁,
# 需要等待其提交后或手动杀掉该会话方可解除MDL锁。
评论