How to query whether the mysql table is locked

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

Blog    

Recommend

How to implement HTML to detect that input is complete and automatically fill in the next content

In the previous article, we have realized the sim...

vue+springboot realizes login verification code

This article example shares the specific code of ...

5 Tips for Protecting Your MySQL Data Warehouse

Aggregating data from various sources allows the ...

How to build a redis cluster using docker

Table of contents 1. Create a redis docker base i...

Detailed Analysis of Explain Execution Plan in MySQL

Preface How to write efficient SQL statements is ...

Centos7 installation of Nginx integrated Lua sample code

Preface The computer I use is a Mac, and the oper...

Detailed tutorial on installing MYSQL under WINDOWS

1. Download the installation package -Choose the ...

Implementation of mysql8.0.11 data directory migration

The default storage directory of mysql is /var/li...

How to use localStorage in JavaScript

If you are a developer looking to get into the wo...

Detailed steps to install Mysql5.7.19 using yum on Centos7

There is no mysql by default in the yum source of...

When Navicat connects to MySQL, it reports 10060, 1045 errors and the location of my.ini

Navicat reports errors 10060 and 1045 when connec...

20 JavaScript tips to help you improve development efficiency

Table of contents 1. Declare and initialize array...

Window environment configuration Mysql 5.7.21 windowx64.zip free installation version tutorial detailed explanation

1. Download mysql-5.7.21-windowx64.zip from the o...

Summary of Vue component basics

Component Basics 1 Component Reuse Components are...

Detailed analysis of javascript data proxy and events

Table of contents Data Brokers and Events Review ...