How to check where the metadata lock is blocked in MySQL Steps: 1. Session 1 execution: start transaction; select *from t1; 2. Session 2 is executed after step 1: drop table t1; At this time, the drop statement of session 2 is blocked. So how do you analyze and view metadata locks? method: 1) Execute show processlist;, and you can see that the drop statement is waiting for the metadata lock. mysql> show processlist; +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ | 5 | system user | | NULL | Connect | 1050234 | Waiting for master to send event | NULL | | 6 | system user | | NULL | Connect | 983193 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 8 | root | localhost | yzs | Sleep | 93 | | NULL | | 9 | root | localhost | yzs | Query | 3 | Waiting for table metadata lock | drop table t1 | | 10 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-------------+-----------+------+---------+---------+-----------------------------------------------------------------------------+------------------+ 5 rows in set (0.00 sec) 2) You can see that the thread of the currently running transaction is trx_mysql_thread_id:8, so what is this thread doing? mysql> select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 17683 trx_state: RUNNING trx_started: 2017-10-18 05:32:46 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 8 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 320 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: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.03 sec) 3) You can see that this thread is executing a select statement. If you execute show engine innodb status; you can see that the transaction is in the sleep state, which means that the transaction statement has been executed but not committed. Execute kill 8 to kill the thread of the transaction. Or check the SQL statements of the business to see if there are any unsubmitted SQL statements. mysql> select * from performance_schema.events_statements_current\G *************************** 1. row *************************** THREAD_ID: 27 EVENT_ID: 15 END_EVENT_ID: 15 EVENT_NAME: statement/sql/select SOURCE: mysqld.cc:962 TIMER_START: 1050544992900922000 TIMER_END: 1050544993740836000 TIMER_WAIT: 839914000 LOCK_TIME: 196000000 SQL_TEXT: select * from t1 DIGEST: 1aa32397c8ec37230aed78ef16126571 DIGEST_TEXT: SELECT * FROM `t1` CURRENT_SCHEMA: yzs OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL MYSQL_ERRNO: 0 RETURNED_SQLSTATE: NULL MESSAGE_TEXT: NULL ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 0 ROWS_SENT: 10 ROWS_EXAMINED: 10 CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 1 SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL If you have any questions, please leave a message or come to the community to discuss. Thank you for reading and I hope it can help you. Thank you for your support of this site! You may also be interested in:
|
<<: VMware Tools installation and configuration tutorial for Ubuntu 18.04
1. Set the list symbol list-style-type: attribute...
Preliminary Notes 1.Differences between Vue2.x an...
Table of contents Index Model B+Tree Index select...
Scenario 1: Html: <div class="outer"...
I searched for three-level linkage on the Interne...
Organize the MySQL 5.5 installation and configura...
Block element p - paragraph pre - format text tabl...
Table of contents Preface Reference Comparison Ma...
Docker is divided into CE and EE. The CE version ...
html <!DOCTYPE html> <html lang="en...
In my recent studies, I found some layout exercis...
1. Introduction The ls command is used to display...
The so-called cascading replication is that the m...
When deleting a table or a piece of data in MySQL...
Table of contents A JSON is built on two structur...