Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is a MySQL metadata lock". In daily operation, I believe many people have doubts about what is a MySQL metadata lock. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what is a MySQL metadata lock?" Next, please follow the editor to study!

1. What is a metadata lock

The full name of MDL is metadata lock, that is, metadata lock, and it can also be called dictionary lock. The main role of MDL is to manage concurrent access to database objects and to ensure metadata consistency. The applicable objects of metadata lock include: table, schema, procedures, functions, triggers, scheduled events, tablespaces.

2. Locking rules

Get rules:

Statements one by one metadata locks one by one, not simultaneously, and perform deadlock detection during the acquisition process.

The DML statement acquires the lock in the order in which the table appears in the statement.

DDL statements, LOCK TABLES, and other similar statements acquire locks in order of name, and may acquire locks in a different order for tables that are used implicitly (such as tables that must also be locked in foreign key relationships).

The write lock request priority of DDL is higher than DML.

3. Simulated locking rules

Two tables with the same table structure, t and t_new, start. Three threads manipulate these tables:

Scene one

Thread 1:

LOCK TABLE t WRITE, t_new WRITE

This statement acquires write locks on t and t_new in the order of table names

Thread 2:

INSERT INTO t VALUES (1)

This statement is in a waiting state because it also needs to get the MDL on table t

Thread 3:

RENAME TABLE t TO t_old, t_new TO t

This statement needs to acquire mutexes on t, t_new and t_old in the order of table names, so it is also in a waiting state.

Thread 1:

UNLOCK TABLES

This statement releases write locks on t and t_new. Thread 3 has a higher priority of adding a write lock to t than thread 2, so thread 3 gets the mutex first on t, then acquires the mutex on t_new and t_old, and releases the lock after renaming. Thread 2 acquires the write lock on t, performs the insert operation, and then releases its lock. The rename operation is performed before the insert.

Scene two

Two tables t and new_t with the same table structure, and the same three threads to manipulate these tables

Thread 1:

LOCK TABLE t WRITE, new_t WRITE

This statement acquires write locks on new_t and t in the order of table names

Thread 2:

INSERT INTO t VALUES (1)

This statement is in a waiting state because it also needs to get the MDL on table t

Thread 3:

RENAME TABLE t TO old_t, new_t TO t

This statement needs to acquire mutexes on new_t, old_t and t in the order of table names, so it is also in a waiting state.

This statement releases write locks on t and new_t. Thread 2 first initiates the lock request for t, so thread 2 first acquires the metadata write lock on t, performs the insert operation, and then releases the lock. Thread 3 first acquires the mutex of new_t and old_t, and then requests the mutex on t, so thread 3 is in a waiting state before thread 2 finishes execution. The rename operation comes after the insert operation.

4. How to monitor metadata locks

The metadata lock-related information is recorded in the performance_schema.metadata_locks table, and the opening method is as follows: open metadata_locks online, and do the following:

-- UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME =' global_instrumentation';-- this value is enabled by default, which can be checked and confirmed. UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME =' wait/lock/metadata/sql/mdl'

If you can stop the library for maintenance, add the following to my.cnf:

[mysqld] performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

5. How to optimize metadata lock

Once the MDL lock occurs, it will have a great impact on the business, because all subsequent access to the table will be blocked, resulting in a connection backlog. We should try our best to avoid the occurrence of MDL locks. Here are some optimization suggestions for reference:

Open the metadata_locks table record MDL lock.

Set the parameter lock_wait_timeout to a small value to make the blocked end stop actively.

Standardize the use of transactions, commit transactions in time, and avoid the use of large transactions.

Enhance the monitoring alarm and find the MDL lock in time.

DDL operations and backup operations are carried out during the business trough.

At this point, the study on "what is a MySQL metadata lock" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.