Deep understanding of the MDL metadata lock for MySQL

• 1 Preface
• 2 MDL Lock and implementation
• 3 Performance and concurrency improvements for MDL Locks
• 4 diagnostics of the MDL Lock

Preface

Long time no update, mainly because inside recently addicted to a cartoon-"New gourd baby brother." Finally smoked cannot, completed this article about the MySQL MDL lock in-depth analysis and introduction. Although a lot of small partners before the analysis, but the total feeling of something less, it took a little time to look over the source. Inside may not be the most ox breaking kernel developers, but think it should be the industry's most telling story of the code farmers, I hope this article can be easy to understand, because the MDL lock is not really good understanding. If the students still have problems, you can also directly see the source file mdl.cc.

MDL Lock and implementation

The MySQL5.5 version introduces the MDL Lock (metadata lock), which is used to resolve or guarantee consistency between DDL operations and DML operations. For example, the following scenario:

Session 1	Session 2
BEGIN;
SELECT * from XXX
	DROP TABLE XXX
SELECT * from XXX

Without the protection of the MDL lock, transaction 2 can perform DDL operations directly and cause a transaction 1 error, as in version 5.1. 5.5 version to join the MDL Lock is to protect this situation, because transaction 1 opened the query, then obtained the MDL Lock, the lock mode is Shared_read, transaction 2 to execute the DDL, you need to obtain a exclusive lock, both mutually exclusive, so transaction 2 needs to wait.

InnoDB layer already has the IS, ix such intent lock, some students think can be used to achieve the above example of concurrency control. But since MySQL is the server-engine architecture, the MDL Lock is implemented in the server. In addition, MDL Locks can implement other granularity locks, such as global locks, library-level locks, and table-space-level locks, which are not directly implemented by the INNODB storage engine layer.

However, as with the implementation of the InnoDB lock, the MDL Lock is similar to the various objects of a tree from top to bottom to lock (the tree lock specifically see: "MySQL Technology insider: InnoDB Storage Engine"). But the level of the MDL lock object is more, simply look at the following level:

The most common 4 MDL lock objects are shown, and the locks that are triggered by common SQL statements are noted. Similar to the INNODB layer, certain types of MDL locks are locked from the top-down layer. Like lock TABLE ... Write SQL statements that first add intention_exclusive locks to the global level, intention_exclusive locks on the schema level, and finally add shared_no_read_ to the table level Write lock.

The most surprising thing about this is that there is a commit object level lock, which is mostly used in XA transactions. For example, distributed transactions have been prepare successful, but before XA commit, other sessions performed flush TABLES with READ lock, then the commit of the distributed transaction needs to wait.

In addition to the annotated object, there are tablespace, FUNCTION, PROCEDURE, event and other object types, in fact, are for concurrency control. Only these are not used in MySQL database, so no longer repeat (of course, also to lazy).

Currently MDL has the following lock mode, the compatibility between the lock visible source code mdl.cc:

Lock mode	corresponding SQL
Mdl_intention_exclusive	Global object, schema object operation will add this lock
Mdl_shared	FLUSH TABLES with READ LOCK
Mdl_shared_high_prio	Valid only for MyISAM storage engines
Mdl_shared_read	Select query
Mdl_shared_write	DML statements
Mdl_shared_write_low_prio	Valid only for MyISAM storage engines
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 ...

Performance and concurrency improvement of MDL Lock

The students will find that the MDL Lock is no less expensive than the INNODB layer's row lock, and this may be a more dense concurrency bottleneck. MySQL versions 5.6 and 5.5 typically perform concurrency tuning by adjusting the following two parameters:

• Cache size for METADATA_LOCKS_CACHE_SIZE:MDL locks
• Metadata_locks_hash_instances: Increases concurrency with sharding, similar to InnoDB AHI

The biggest improvement of the MySQL 5.7 MDL Lock is that the mechanism of the MDL Lock is implemented through the lock free algorithm, thus improving the overall performance of the database under multi-core concurrency.

diagnosis of MDL Lock

MySQL 5.7 version did not provide a convenient way to view the MDL Lock, GitHub has a project for Mysql-plugin-mdl-info, through the way of plug-ins to view, very idea of implementation, great. Fortunately, the official is aware of this problem, so in MySQL 5.7 in the Performance_schea library, a new table metadata_locks, used to view the MDL lock that is quite convenient:

However, the default PS does not open this feature, you need to manually monitor the WAIT/LOCK/METADATA/SQL/MDL to open:

Update Set enabled = ' Yes ', timed = '    Yes'  where='wait/lock/metadata/sql/mdl  ';

MySQL 5.7 Last improvement for MDL in mysqldump, can some students know where they are? The first one to get the right answer is inside's signed "MySQL kernel: InnoDB storage Engine Volume 1" book.

Deep understanding of the MDL metadata lock for MySQL