mysql查询事务与元数据锁metadata lock

lovedb 2019-11-20

242

联系方式

前言

今天分析某客户数据库问题，发现查询事务也会阻塞ddl事务，导致thread_connected过高，数据库负载突增。作了一系列小实验，故有此文。

相关文章

如何使用mysql 5.6 information schema定位事务锁信息

查询事务会阻塞ddl事务
ddl事务会阻塞dml事务
元数据锁概述
获取元数据锁metadata lock2种方式

查询事务会阻塞ddl事务

ddl事务会阻塞dml事务

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
| Id | User | Host      | db                 | Command | Time | State                           | Info                                                             |
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
|  2 | root | localhost | zxydb              | Sleep   | 2407 |                                 | NULL                                                             |
| 17 | root | localhost | zxydb              | Query   |    8 | Waiting for table metadata lock | insert into t_transaction select 1,1,1,1,1,1,1,1,1,1,1 from dual |
| 21 | root | localhost | zxydb              | Query   |   13 | copy to tmp table               | alter table t_transaction add v int                              |
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
5 rows in set (0.00 sec)

元数据锁概述

元数据锁用于保锁事务处理期间数据对象定义结束的完整性及一致性，数据库对象包括：数据库，表，索引，触发器，函数等。

元数据锁官方手册

25 MySQL Performance Schema
25.12 Performance Schema Table Descriptions
25.12.12 Performance Schema Lock Tables
25.12.12.1 The metadata_locks Table

获取元数据锁metadata lock 2种方式

查询show processist

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
| Id | User | Host      | db                 | Command | Time | State                           | Info                                                             |
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
|  2 | root | localhost | zxydb              | Sleep   | 2407 |                                 | NULL                                                             |
| 17 | root | localhost | zxydb              | Query   |    8 | Waiting for table metadata lock | insert into t_transaction select 1,1,1,1,1,1,1,1,1,1,1 from dual |
| 21 | root | localhost | zxydb              | Query   |   13 | copy to tmp table               | alter table t_transaction add v int                              |
+----+------+-----------+--------------------+---------+------+---------------------------------+------------------------------------------------------------------+
5 rows in set (0.00 sec)

查询performance_schema之metadata_locks

(注：须启用其instrument,其表才会记录元数据锁）

mysql> desc performance_schema.metadata_locks;
+-----------------------+---------------------+------+-----+---------+-------+
| Field                 | Type                | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| OBJECT_TYPE           | varchar(64)         | NO   |     | NULL    |       |
| OBJECT_SCHEMA         | varchar(64)         | YES  |     | NULL    |       |
| OBJECT_NAME           | varchar(64)         | YES  |     | NULL    |       |
| OBJECT_INSTANCE_BEGIN | bigint(20) unsigned | NO   |     | NULL    |       |
| LOCK_TYPE             | varchar(32)         | NO   |     | NULL    |       |
| LOCK_DURATION         | varchar(32)         | NO   |     | NULL    |       |
| LOCK_STATUS           | varchar(32)         | NO   |     | NULL    |       |
| SOURCE                | varchar(64)         | YES  |     | NULL    |       |
| OWNER_THREAD_ID       | bigint(20) unsigned | YES  |     | NULL    |       |
| OWNER_EVENT_ID        | bigint(20) unsigned | YES  |     | NULL    |       |
+-----------------------+---------------------+------+-----+---------+-------+
10 rows in set (0.02 sec)

mysql> select object_schema,object_type,object_name,lock_type,lock_duration,lock_status,source,owner_thread_id,owner_event_id 
       from performance_schema.metadata_locks 
       where object_schema='zxydb' and 
             object_type='TABLE'   and
             object_name='t_transaction';


+--------------------+-------------+----------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| object_schema      | object_type | object_name    | lock_type           | lock_duration | lock_status | source            | owner_thread_id | owner_event_id |
+--------------------+-------------+----------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
| zxydb              | TABLE       | t_transaction  | SHARED_UPGRADABLE   | TRANSACTION   | GRANTED     | sql_parse.cc:6020 |              46 |             49 |  ---持挂   lock_status=granted
| zxydb              | TABLE       | t_transaction  | SHARED_WRITE        | TRANSACTION   | PENDING     | sql_parse.cc:6020 |              42 |             49 |  ---等待锁 lock_status='pending'
+--------------------+-------------+----------------+---------------------+---------------+-------------+-------------------+-----------------+----------------+
6 rows in set (0.00 sec)

培训课件

(收费20元,课件已上传百盘云盘,方便大家获取)

mysql

文章转载自lovedb，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

mysql查询事务与元数据锁metadata lock

评论