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

This article mainly introduces mysql how to query locked tables, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Methods: 1, use "show OPEN TABLES where In_use > 0;" command to check the locked state 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.

1. Check to see if the table is locked:

(1) execute directly on the mysql command line: show engine innodb status\ G.

(2) look at the sql statement that caused the deadlock, analyze the index, and then optimize the sql.

(3) then show processlist, look at the sql statement that caused the deadlock to take a long time.

(4) show status like'% lock%.

two。 Check the locked status of the table and the steps to end the deadlock:

(1) check the locked status of the table: show OPEN TABLES where In_use > 0; this statement records the current locked table status.

(2) query process: show processlist query table is locked process; query the corresponding process killid.

(3) analyze the SQL of the locked table: analyze the corresponding SQL, index the table, index the commonly used fields, and index the associated fields of the table.

(4) check what is being locked: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS.

(5) check what is waiting for the lock: SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS.

Extended data

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 table that has been used up is being closed. This is a quick operation, and if not, you should make sure that the disk space is full or that the disk is under a heavy load.

Connect Out: the replication slave server is connecting to the master server.

Copying to tmp table on disk: because the temporary result set is larger than tmp_table_size, temporary tables are being converted from memory storage to disk storage to save memory.

Creating tmp table: a temporary table is being created to hold some of the query results.

Deleting from main table: the server is performing the first part of the multi-table deletion, and the first table has just been deleted.

Deleting from reference tables: the server is performing the second part of the multi-table deletion and is deleting records for other tables.

Flushing tables: executing FLUSH TABLES, waiting for another thread to close the data table.

Killed: if a kill request is sent to a thread, the thread will check the kill flag bit and abandon the next kill request. MySQL checks the kill flag bit in each main loop, but in some cases the thread may take a short time to die. If the thread is locked by another thread, the kill request takes effect as soon as the lock is released.

Locked: locked by other queries.

Sending data: the record of the SELECT query is being processed and the result is 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 very fast, unless disturbed by other factors. For example, a data table cannot be opened by another thread until the execution of an ALTER TABLE or LOCK TABLE statement is complete. Trying to open a table.

Removing duplicates: a query in SELECT DISTINCT mode is being executed, but MySQL cannot optimize those duplicate records in the previous phase. Therefore, MySQL needs to remove the duplicate records again and then send the results to the client.

Reopen table: a lock on a table is acquired, but the lock cannot be acquired until the table structure has been modified. The lock has been released, the datasheet has been closed, and an attempt is being made to reopen the datasheet.

Repair by sorting: the repair instruction is being sorted to create an index.

Repair with keycache: the repair instruction is using the index cache to create new indexes one by one. It will be slower than Repair by sorting.

Searching rows for update: we are talking about finding qualified records for updating. It must be done before UPDATE modifies the relevant records.

Sleeping: waiting for a new request from the client.

System lock: waiting for an external system lock to be acquired. 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 get a lock table to insert a new record.

Updating: searching for matching records and modifying them. User Lock: waiting for GET_LOCK ().

Waiting for tables: this thread is notified that the data table structure has been modified and needs to be reopened to get the new structure. Then, in order to reopen the 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 a new request.

Thank you for reading this article carefully. I hope the article "how to query locked tables in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.