Method: 1. Use the "show OPEN TABLES where In_use > 0;" command to check the locked status of the table; 2. Use the "SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS" command to query the locked table.
The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.
How does mysql query the locked table
1. Check whether the table is locked:
(1) Directly in Mysql command line execution: show engine innodb status\G.
(2) Check the sql statement that caused the deadlock, analyze the index situation, and then optimize the sql.
(3) Then show processlist to check the SQL statements that cause the deadlock to take a long time.
（4）show status like ‘%lock%.
2. Check the table lock status and end deadlock steps:
(1) Check the table lock status: show OPEN TABLES where In_use > 0; This statement records the current lock table status .
(2) Query process: show processlist query table locked process; query the corresponding process killid.
(3) Analyze the SQL that locks the table: analyze the corresponding SQL, add indexes to the table, add indexes to common fields, and add indexes to table related fields.
(4) Check what is being locked: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS.
(5) View things waiting for locks: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS.
Extended information
MySQL lock status view command:
Checking table: Checking the data table (this is automatic).
Closing tables: The modified data in the table is being flushed to disk, and the tables that have been used up are being closed. This is a quick operation, but if this is not the case, you should verify that the disk space is full or that the disk is under heavy load.
Connect Out: The replication slave server is connecting to the master server.
Copying to tmp table on disk: Since the temporary result set is larger than tmp_table_size, the temporary table is being converted from memory storage to disk storage to save memory.
Creating tmp table: A temporary table is being created to store some query results.
deleting from main table: The server is performing the first part of a multi-table deletion and has just deleted the first table.
Deleting from reference tables: The server is performing the second part of multi-table deletion and is deleting records from other tables.
Flushing tables: FLUSH TABLES is being executed, waiting for other threads to close the data table.
Killed: If a kill request is sent to a thread, the thread will check the kill flag and give up the next kill request. MySQL will check the kill flag in each main loop, but in some cases the thread may die after a short period of time. If the thread is locked by another thread, the kill request will take effect immediately when the lock is released.
Locked: Locked by other queries.
Sending data: The records of the SELECT query are being processed and the results are being sent to the client.
Sorting for group: Sorting for GROUP BY.
Sorting for order: Sorting for ORDER BY.
Opening tables: This process should be fast unless interfered by other factors. For example, the data table cannot be opened by other threads until the ALTER TABLE or LOCK TABLE statement is executed. Trying to open a table.
Removing duplicates: A SELECT DISTINCT query is being executed, but MySQL cannot optimize out those duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records again before sending the results to the client.
Reopen table: A lock on a table is obtained, but the lock cannot be obtained until the table structure is modified. The lock has been released, the data table has been closed, and an attempt is made to reopen the data table.
Repair by sorting: The repair directive is sorting to create the index.
Repair with keycache: The repair directive is utilizing the index cache to create new indexes one by one. It will be slower than Repair by sorting.
Searching rows for update: Searching for records that meet the conditions for update. It must be completed before UPDATE is to modify the related records.
Sleeping: Waiting for the client to send a new request.
System lock: Waiting to obtain an external system lock. If you are not currently running multiple mysqld servers requesting the same table at the same time, you can disable external system locks by adding the --skip-external-locking parameter.
Upgrading lock: INSERT DELAYED is trying to obtain a lock table to insert new records.
Updating: Searching for matching records and modifying them. User Lock: Waiting for GET_LOCK().
Waiting for tables: The thread is notified that the data table structure has been modified and the data table needs to be reopened to obtain the new structure. Then, in order to reopen the data table, you must wait until all other threads close the table.
waiting for handler insert: INSERT DELAYED has processed all pending insert operations and is waiting for new requests.
Recommended learning:mysql video tutorial
The above is the detailed content of How to query a locked table in mysql. For more information, please follow other related articles on the PHP Chinese website!