MySQL checks whether the table is locked

MySQL method to check whether the table is locked: first enter the command window; then execute the command "show engine innodb status\G;" to check the sql statement causing the deadlock and analyze the index situation.

Can be executed directly on the mysql command line: show engine innodb status\G;

View the sql statement causing the deadlock, Analyze the index situation, then optimize the sql and then show processlist;

Recommended course: MySQL Tutorial.

mysql> show status like 'Table%';

+----------------------------+----------+
| Variable_name        | Value |
+----------------------------+----------+
| Table_locks_immediate | 105         |
| Table_locks_waited   | 3           |
+----------------------------+----------+

Table_locks_immediate refers to the number of times that table-level locks can be obtained immediately

Table_locks_waited refers to the number of times that table-level locks cannot be obtained immediately The number of times you need to wait to obtain a table-level lock

show OPEN TABLES where In_use > 0; This statement records the current lock table status

Added under the [mysqld] option of the my.ini configuration file ：

slow_query_log=TRUE

slow_query_log_file=c:/slow_query_log.txt

long_query_time=3

After the addition is completed, remember to restart the mysql service to take effect Log the output. Finally, Smile entered the command in the MySQL client:

show variables like '%quer%'; Check if it is OK

Enable mysql slow query log

View configuration:

//查看慢查询时间
show variables like "long_query_time";默认10s
//查看慢查询配置情况
show status like "%slow_queries%";
//查看慢查询日志路径
 show variables like "%slow%";
-slow_query_log是否记录慢查询。用long_query_time变量的值来确定“慢查询”。
-slow_query_log_file慢日志文件路径
-long_query_time慢日志执行时长（秒），超过设定的时间才会记日志

Let’s get back to the point, back to the starting point of our problem Mysql query table deadlock and the table steps to end the deadlock

1. Query whether the table is locked show OPEN TABLES where In_use > 0;

2. Query the process

show processlist Query the corresponding process===and then kill the id

Supplement:

View the transaction being locked

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

The above is the detailed content of MySQL checks whether the table is locked. For more information, please follow other related articles on the PHP Chinese website!