Detailed explanation of locked table query in Mysql and how to unlock

Preface

When a table in MySQL is locked, it may cause the query to slow down or the table is completely inaccessible. The way to deal with this depends on the reason for the lock and what you want to achieve. Here are some common steps and tips to help you solve the problem of locked tables:

1. Identify the lock situation

First, you need to determine which tables are locked and what causes the lock. You can use the following command to view the current lock situation:

SHOW OPEN TABLES WHERE In_use > 0;

This command displays all tables being used and their status. ifIn_useIf the value of the column is greater than 0, it means that the table is being locked.

2. Find locked source

Understand which process or transaction holds the lock. You can use the following command to view the currently active process list:

SHOW FULL PROCESSLIST;
-- This statement can be filtered，More convenient
SELECT * FROM information_schema.PROCESSLIST where DB = 'ipd-sup';

This command lists all currently running threads, including their status, time, query and other information. Find processes that run for a long time or have a status of "Locked".

3. Analyze and terminate the lock process

analyze: Check the SQL statement that locks the process and try to understand why it causes the lock. It may be due to long-running queries, uncommitted transactions, etc.
termination: If you determine that a process is the source of the problem, you can consider terminating the process. Use the following command:
```
KILL [CONNECTION | QUERY] process_id;
-- processIDThat's the aboveSQLQueryedidList。for exampleidfor1124511
KILL 1124511;
```
- KILL CONNECTIONwill terminate the entire connection, andKILL QUERYOnly the currently executing query is terminated.

4. Check and repair the table

Sometimes the table may be locked due to some error. You can try to check and repair tables with the following command:

CHECK TABLE table_name;
REPAIR TABLE table_name;

5. Restart MySQL service

If none of the above methods can solve the problem, you can consider restarting the MySQL service. This will usually release all locks, but will also interrupt all ongoing transactions, so please be cautious.

Attachment: MySQL query lock related commands

#1. Query whether the table is lockedshow OPEN TABLES where In_use &gt; 0;
#2. View the locked transactionSELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#3. View transactions waiting for locksSELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
select * from information_schema.innodb_trx;
select * from innodb_trx;
#4. View lock blocking thread informationshow processlist;
show engine innodb status;
#5. Check the table lock situation:show status like 'table%';
#6. Check the InnoDB_row_lock status variable to analyze the competition for row locks on the system:show status like 'InnoDB_row_lock%'

Summarize

This is the article about locked table query in Mysql and how to unlock it. For more related contents of Mysql locked table query and unlocking, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!