Shulou(Shulou.com)06/01 Report--

The following mainly brings you what the role of Metadata Lock in the MySQL database, I hope that these contents can bring you practical use, which is also my main purpose of editing the role of Metadata Lock in the MySQL database. All right, don't talk too much nonsense, let's just read the following.

After truncate table on the main library, it is found that the library latency begins to increase, and the following information can be seen in show processlist.

72105289 system user NULL Connect 2996302 Waiting for master to send event NULL 0 0

72105290 system user dbname Connect 491 Waiting for table metadata lock truncate table tblname 0 0

MySQL uses metadata lock to control concurrent access to database objects and to ensure data consistency. The inability to execute DDL statements on a table in use in MySQL is achieved by getting the metadata lock of the table, which prevents changes to the table structure. That is, if a table is being used by a transaction, another session cannot DDL the table until the transaction ends.

Waiting for table metadata lock, indicating that there was a transaction using the tblname table before truncate table, and the transaction is not finished, and no further information can be seen in show processlist.

However, the innodb_ innodb_ table in information_schema can provide information about transactions running in InnoDB, such as whether the transaction is waiting for a lock, the start time of the transaction, the SQL running in the transaction, and so on.

Look at the innodb_ trx table and find a transaction, Id is 90916563 (trx_mysql_thread_id corresponds to Id in show processlist), the start time is 2019-02-12 13:26:56, the time to execute truncate table in the main database is 17:35, and its running time is somewhat abnormal; another trx_query is NULL, you can judge that the transaction is likely to be stuck here and did not end normally, preventing the continuation of truncate table.

Mysql > select * from INNODB_TRX\ G

* * 1. Row *

Trx_id: 146633268809

Trx_state: RUNNING

Trx_started: 2019-02-12 13:26:56

Trx_requested_lock_id: NULL

Trx_wait_started: NULL

Trx_weight: 0

Trx_mysql_thread_id: 90916563

Trx_query: NULL

Trx_operation_state: NULL

Trx_tables_in_use: 0

Trx_tables_locked: 0

Trx_lock_structs: 0

Trx_lock_memory_bytes: 1136

Trx_rows_locked: 0

Trx_rows_modified: 0

Trx_concurrency_tickets: 0

Trx_isolation_level: REPEATABLE READ

Trx_unique_checks: 1

Trx_foreign_key_checks: 1

Trx_last_foreign_key_error: NULL

Trx_adaptive_hash_latched: 0

Trx_adaptive_hash_timeout: 0

Trx_is_read_only: 0

Trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

You can see from show processlist that Id is 90916563 thread and its Time is 1294. Kill drops the thread and the master-slave replication returns to normal.

90916563 dbname_read 192.168.2.36:63063 dbname Sleep 1294 NULL 100 100

To view the details of metadata lock, you can use the metadata_ locks table under Performance Schema.

For the above about the role of Metadata Lock in the MySQL database, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.