How to query whether the mysql table is locked

Specific method:

(Recommended tutorial: MySQL database learning tutorial)

Check the table lock status

# Check which tables are locked show OPEN TABLES where In_use > 0;

View the SQL statement that caused the deadlock

# Query the runtime information of the innodb engine show engine innodb status;

Query process

#Query all processes show processlist;

Unlock (delete process)

# Delete process kill id;

View what is being locked

# View the things being locked SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

View things waiting for locks

# View transactions waiting for locks SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Supplementary content

MySQL lock status view command

Status	meaning
Checking table	Checking data tables (this is automatic).
Closing tables	The modified data in the table is being flushed to disk, and the table that has been used is being closed. This is a very quick operation, but if it is not, you should check to see if the disk is full or if 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	Creating a temporary table to hold partial query results.
deleting from main table	The server is executing the first part of a multiple-table delete and has just deleted the first table.
deleting from reference tables	The server is executing the second part of a multi-table delete 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 abandon the next kill request. MySQL checks the kill flag in each main loop, but in some cases it may take a while for the thread to die. If the thread is locked by other threads, the kill request will take effect immediately when the lock is released.
Locked	Locked by another query.
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 quick unless interfered with by other factors. For example, the table cannot be opened by other threads until the ALTER TABLE or LOCK TABLE statement is completed. Trying to open a table.
Removing duplicates	A SELECT DISTINCT query is being executed, but MySQL was unable to optimize away duplicate records in the previous stage. Therefore, MySQL needs to remove duplicate records again and then send the results to the client.
Reopen table	A lock on a table is obtained, but the lock can only be obtained after the table structure is modified. The lock has been released, the table has been closed, and an attempt is being made to reopen the table.
Repair by sorting	Fix instructions being sorted to create index.
Repair with keycache	The repair instruction is creating new indexes one by one using the index cache. It will be slower than Repair by sorting.
Searching rows for update	The records that meet the conditions are being found for update. It must be done before the UPDATE can modify related records.
Sleeping	Waiting for new request from client.
System lock	Waiting to acquire an external system lock. If you are not 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 acquire a table lock to insert a new record.
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 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. This notification is generated in the following situations: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
waiting for handler insert	INSERT DELAYED has processed all pending insert operations and is waiting for new requests.

This is the end of this article about how to query whether a MySQL table is locked. For more information about how to query whether a MySQL table is locked, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

Will Update in a Mysql transaction lock the table?
Analysis of the locking mechanism of MySQL database
Detailed explanation of mysql deadlock checking and deadlock removal examples
The normal method of MySQL deadlock check processing
Determine whether MySQL update will lock the table through examples
Pessimistic locking and optimistic locking in MySQL
Detailed explanation of the meaning and difference between MySQL row locks and table locks
Understanding and application analysis of mysql pessimistic locking and optimistic locking
MySQL 8.0.19 supports locking an account after entering an incorrect password three times (example)
Summary of MySQL lock related knowledge

<<: How to install Solr 8.6.2 in Docker and configure the Chinese word segmenter

>>: Mysql join query syntax and examples