MetadataLocking in MySQL _ MySQL

MySQL introduced metadatalock in 5.5. as the name suggests, metadatalock is not designed to protect data in tables, but to protect databaseobjects (database objects. Including table structure, schema, stored procedures, functions, triggers, and mysql scheduling events... mySQL introduced metadata lock in 5.5. as the name implies, metadata lock does not protect the data in tables, but database objects (database objects. Including table structure, schema, stored procedures, functions, triggers, and mysql scheduling events (events ). the most important thing to understand metadata lock is to put metadata lock into the semantics of database transactions. Metadata lock is used to ensure the security of all metadata (metadata, that is, database objects) involved in a transaction during execution. For example, if you select a table in a transaction, you must ensure that the table will not be deleted or modified before your transaction is completed.

1. Functions of metadata lock

MySQL uses metadata locking to manage concurrent access to database objects and to ensure data consistency. metadata locking applies not just to tables, but also to schemas and stored programs (procedures, functions, triggers, and scheduled events ).

Metadata lock manages concurrent access to database objects to ensure data consistency.

2. metadata lock will cause performance loss and lock contention

Metadata locking does involve some overhead, which increases as query volume increases. Metadata contention increases the more that multiple queries attempt to access the same objects.

The introduction of metadata lock causes certain performance loss. The more accesses to the same database object, the more likely it will be to compete for the metadata lock on the object.

3.

Metadata locking is not a replacement for the table definition cache, and its mutexes and locks differ from the LOCK_open mutex.

Metadata lock is not used to replace the table definition cache. Its mutex and lock are different from LOCK_open mutex.

4.

To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. the server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. this locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.

For a running transaction, you must obtain the metadata lock on all database objects to be accessed at the beginning of the transaction, and then release the metadata lock on those database objects at the end of the transaction. the relationship between transactions and metadata lock is extremely close: there must be metadata lock when there is a transaction, and the transaction will be released when it ends. Metadata lock prevents the database objects in the transaction from being modified. for example, it prevents the table structure in the transaction from being modified. Therefore, DDL execution on the database objects in the transaction will be blocked until the transaction ends.

5.

This principle applies not only to transactional tables, but also to nontransactional tables. Suppose that a session begins a transaction that uses transactional table t and nontransactional table nt as follows:

Start transaction;

SELECT * FROM t;

SELECT * FROM nt;

The server holds metadata locks on both t and nt until the transaction ends. if another session attempts a DDL or write lock operation on either table, it blocks until metadata lock release at transaction end. for example, a second session blocks if it attempts any of these operations:

Drop table t;

Alter table t ...;

Drop table nt;

Alter table nt ...;

Lock table t... WRITE;

Metadata lock not only involves tables in the transaction engine, but also applies to table. metadata lock in non-transaction engines, which not only blocks DDL but also lock table table_name write statements.

6.

If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.

If the syntax of an SQL statement is correct but the execution fails, the metadata lock on it will not be released immediately, but will be released after the transaction ends. This is to ensure log consistency.

7.

In autocommit mode, each statement is in effect a complete transaction, so metadata locks acquired for the statement are held only to the end of the statement.

In the automatic commit mode (mysql command line tool uses the automatic commit mode by default), metadata lock is released immediately after the statement is executed, because it is a single statement transaction automatically submitted.

8.

Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.

The metadata lock in the transaction is released until the transaction ends, but there is a special case: the prepare (a dynamic statement generally used in the stored procedure) statement in the transaction releases the corresponding metadata lock immediately after execution.

9.

Before MySQL 5.5, when a transaction acquired the equivalent of a metadata lock for a table used within a statement, it released the lock at the end of the statement. this approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements cocould be written to the binary log in the wrong order.

MySQL 5.5 introduces metadata lock, replacing the equivalents in previous versions.

However, metadata lock differs from her previous equivalents: metadata lock is released only after the transaction ends, and her equivalents are released immediately after the statement is executed. Metadata lock is used to ensure that the binary log order is correct.

10. Experiment 1 (lock table xxx write statement; mysql5.6.27 in Centos)

Run the following command on Terminal:

Mysql> lock table cats write;

Query OK, 0 rows affected (0.01 sec)

Then run the following command in Terminal B:

Select * from cats;

You will find blocked.

Then run the following command in Terminal:

Mysql> show processlist;

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

| 1 | root | localhost | ngx_lua | Query | 2940 | Waiting for table metadata lock | select * from cats |

| 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist |

| 3 | root | localhost | NULL | Sleep | 2913 | NULL |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

3 rows in set (0.00 sec)

You can see that the reason why select * from cats is blocked is: Waiting for table metadata lock

The Sleep contains the lock table cats wirte statement. It also holds the metadata lock on the cats table and rejects any other transaction's application for the metadata lock.

Here you may ask: what about MVCC? A good select statement can use MVCC without a lock?

So maybe this is a difference between MySQL and Oracle.

Then we execute kill 3 to try to kill the session lock table cats write and expect it to be metadata lock:

Mysql> kill 3

->;

Query OK, 0 rows affected (0.00 sec)

Mysql> show processlist;

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

| 1 | root | localhost | ngx_lua | Query | 3605 | Waiting for table metadata lock | select * from cats |

| 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + -------------------- +

2 rows in set (0.00 sec)

Mysql> unlock table cats;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Cats' at line 1

Mysql> unlock tables;

Query OK, 0 rows affected (0.01 sec)

Mysql> show processlist;

+ ---- + ------ + ----------- + --------- + ------ + ------- + ------------------ +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ------ + ----------- + --------- + ------ + ------- + ------------------ +

| 1 | root | localhost | ngx_lua | Sleep | 3757 | NULL |

| 2 | root | localhost | ngx_lua | Query | 0 | init | show processlist |

+ ---- + ------ + ----------- + --------- + ------ + ------- + ------------------ +

2 rows in set (0.01 sec)

Mysql>

However, kill 3 does not release the metadata lock. The unlock tables statement is used to release the metadata lock. The select statement can also be executed.

Note: the lock table cats read statement does not block other statements by holding metadata lock.

11. Experiment 2

First, modify the autocommit parameter in Terminal:

Mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

Mysql> show session variables like 'autocommit ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Autocommit | OFF |

+ --------------- + ------- +

1 row in set (0.00 sec)

Mysql> commit;

Query OK, 0 rows affected (0.00 sec)

Mysql> select * from cats;

+ ---- + ------ +

| Id | name |

+ ---- + ------ +

| 3 | NULL |

| 2 |

| 1 | Andy |

+ ---- + ------ +

3 rows in set (0.01 sec)

First, modify the autocommit parameter of the session to off, and then start a transaction. Note that the transaction has not been committed.

Execute a DDL statement in Terminal B:

It is found to be blocked. The transaction that is not committed is blocked because it holds metadata lock, which causes the DDL statement to be blocked.

Mysql> show processlist;

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + ---------------------------------- +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + ---------------------------------- +

| 1 | root | localhost | ngx_lua | Query | 0 | init | show processlist |

| 2 | root | localhost | ngx_lua | Query | 177 | Waiting for table metadata lock | alter table cats drop index name |

| 4 | root | localhost | NULL | Sleep | 322 | NULL |

+ ---- + ------ + ----------- + --------- + ------ + --------------------------------- + ---------------------------------- +

3 rows in set (0.00 sec)

After manual commit, the DDL blocking ends and the execution is successful.

Mysql> alter table cats drop index name;

Query OK, 0 rows affected (4 min 30.81 sec)

Records: 0 Duplicates: 0 Warnings: 0

We can see that the traffic is blocked (4 min 30.81 sec)

The above two experiments show that DDL statements, lock table xxx write, and transactions are mutually exclusive to metadata lock.

12. Experiment 3

Run the following command in Terminal A (autocommit = off:

Mysql> show variables like 'autocommit ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Autocommit | OFF |

+ --------------- + ------- +

1 row in set (0.00 sec)

Mysql> update cats set name = 'linus' where id = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Then run the following command in Terminal B (autocommit = on:

Mysql> update cats set name = 'strup' where id = 3;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

It can be seen that Terminal B is not blocked.

Even if both A and B are autocommit = off and are not committed, they all exist in transactions and will not block each other. This indicates that normal update, select, and delete won't compete on metadata lock, that is, multiple running transactions can hold metadata lock on the same database object at the same time (because these non-DDL statements do not modify database objects, they modify table data rather than table structure ).

13. Experiment 4

First, set autocommit = off on Terminal A, and then execute an update, select, and delete statement as needed:

Mysql> show variables like 'autocommit ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Autocommit | OFF |

+ --------------- + ------- +

1 row in set (0.00 sec)

Mysql> update uu_test set sex = 'M' where id = 1;

Then execute a DDL statement in Terminal B:

Alter table uu_test add index (userId );

As A result, you will find that the DDL in Terminal B will be blocked all the time. in view on Terminal:

Mysql> show processlist;

+ ---- + ------ + ----------- + ------ + --------- + ------ + --------------------------------- + --------------------------------------- +

| Id | User | Host | db | Command | Time | State | Info |

+ ---- + ------ + ----------- + ------ + --------- + ------ + --------------------------------- + --------------------------------------- +

| 1 | root | localhost | aazj | Query | 0 | init | show processlist |

| 2 | root | localhost | aazj | Query | 351 | Waiting for table metadata lock | alter table uu_test add index (userId) |

| 4 | root | localhost | NULL | Sleep | 2900 | NULL |

+ ---- + ------ + ----------- + ------ + --------- + ------ + --------------------------------- + --------------------------------------- +

3 rows in set (0.00 sec)

It is found that it is blocked by the metadata lock held by A for the submitted transaction. The danger of uncommitted transactions can be seen !!!!!! It will always hold metadata lock.

14. Experiment 5:

Execute A long DDL statement in Terminal:

Mysql> alter table uu_test add index (user_homeTel );

Query OK, 0 rows affected (12.86 sec)

Records: 0 Duplicates: 0 Warnings: 0

Then, run the following command on Terminal B before executing the DDL statement:

Mysql> update uu_test set user_Sex = 'M' where userId = 1;

Query OK, 0 rows affected (0.14 sec)

Rows matched: 256 Changed: 0 Warnings: 0

During DDL execution, the update, select, delete, and other statements in other transactions are not blocked. That is to say, the DDL statement holds the metadata lock instantaneously and will not hold it until it is executed. Pay attention to this. This is the difference between the DDL statement and the transaction and the lock table xxx write statement: the DDL statement does not hold metadata lock during execution, but temporarily holds metadata lock at the beginning of execution and is released immediately; the transaction will always hold metadata lock during the transaction; the lock table xxx write statement will also always hold the metadata lock specified unlock statement to issue.

15. Experiment 6 (the greatest threat to DDL ):

First, set autocommit = off in Terminal A. then execute A select/update/delete statement without submitting the statement, occupying metadata lock:

Mysql> select userId, user_Sex from uu_test limit 2;

+ -------- + ---------- +

| UserId | user_Sex |

+ -------- + ---------- +

| 1 | M |

| 2 | F |

+ -------- + ---------- +

2 rows in set (0.09 sec)

Then execute a DDL statement in Terminal B, which is obviously blocked by the above metadata lock:

Then, on the C terminal, run the select, update, and delete statements for the same table uu_test. this is amazing !!!!!

The select statement uu_test in the same table in the C terminal is blocked !!!!!!

Check show processlist in Terminal D:

You can see that the DDL statement alter table uu_test add index (user_QQ) is blocked by uncommitted transactions, and the DDL statement then blocks all the subsequent statements in the transaction for the same table uu_test. Think they all want to get metadada lock. This should be the biggest threat to DDL statements. Likewise, it can be inferred that a long transaction holding metadata lock for a long time will block the mutex application of other DDL statements on metada lock, and then the DDL statement will block all subsequent statements involving the database objects. Here, according to our normal logic, the statements in C should not be blocked? Is it to prevent the application of DDL statements for metadata lock from getting hungry. Therefore, the statements in C are blocked. Or does the application for metadata lock maintain a FIFO queue?

Then we execute commit in Terminal A: commit; then DDL statement in B gets metadata lock immediately, and then release immediately; then select in C also gets metadata lock. the DDL statement in B is executed after C is executed for a long time. This also indicates that the DDL statement holds the metadata lock temporarily and does not hold the lock during execution (otherwise, C will not execute the lock before B ).

16. conclusion:

1) metadata lock protects metadata, that is, database object (table structure and other metadata), rather than data in the table;

2) each database object involved in a running transaction must obtain the metadata lock and release it at the end of the transaction (except for the parepare statement );

3) DDL statements, lock table xxx write, and transactions are mutually exclusive to metadata lock;

Normal update, select, and delete operations do not compete on metadata lock, that is, multiple running tasks can simultaneously hold metadata lock on the same database object.

4) the default mysql terminal is autocommit = on. do not change the mysql tool to autocommit = off by default, while the JDBC connection is autocommit = off by default;

5) metadata lock because every transaction must be obtained first and released at the end of the transaction, there must be no large transaction in MySQL, especially long-running transactions;

Otherwise, it will cause long-term occupation of metadata lock. It will block any DDL statements and lock table... write statements related to the database object in other transactions;

6) the difference between DDL statements and transactions and lock table xxx write statements:

DDL statements do not hold metadata lock during execution. Instead, they only hold metadata lock at the beginning of execution and are immediately released;

The transaction will always hold metadata lock during the transaction; the lock table xxx write statement will also hold metadata lock until the unlock statement is unlocked.

7) long transactions and lock table... write statements hold metadata lock for a long time. Therefore, before executing DDL statements, use the show processlist statement to check the table involved in DDL statements.

Whether it is accessed by a long-running thing. Otherwise, DDL statements may be blocked by metadata lock. What is terrible is not DDL, but a long transaction.

8) DDL statements executed in mysql command line tools will not be affected by autocommit = on/off. DDL statements automatically start transactions and automatically commit transactions at the end;

9) The greatest harm of DDL statements:

Transactions that have not been committed or long transactions hold metadata lock for a long time will block the subsequent DDL statement's mutex application for metada lock,

Then, the DDL statement's mutex application for metadata lock will block all subsequent statements related to the database objects, because they also need to apply for metadata lock.