Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to check whether the table is locked by MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

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

Look at the sql statement that caused the deadlock, analyze the index, then optimize 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 a table-level lock can be acquired immediately

Table_locks_waited refers to the number of times you have to wait when a table-level lock cannot be acquired immediately.

Show OPEN TABLES where In_use > 0; this statement records the current lock table status

Add the following 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, remember to restart the mysql service to take effect on the record output. Smile and finally enter the command in the MySQL client:

Show variables like'% quer%'; checks whether YHA is Ok.

Open mysql slow query log

View the configuration:

/ / View slow query time show variables like "long_query_time"; default 10s// to view slow query configuration show status like "% slow_queries%"; / / view slow query log path show variables like "% slow%";-whether slow query is recorded by slow_query_log. Use the value of the long_query_time variable to determine the "slow query". -slow_query_log_file slow log file path-long_query_time slow log execution time (seconds). Log will be recorded only after the set time.

Back to the starting point of our question, Mysql queries table deadlocks and table steps to end deadlocks

1. Query whether to lock the table show OPEN TABLES where In_use > 0

2. Query process

Show processlist queries the corresponding process = and then kill id

Add:

View transactions that are being locked

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

View transactions waiting for locks

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

About MySQL how to check whether to lock the table to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.