High-performance MySQL Reading Notes: Find out who holds the lock

High-performance MySQL Reading Notes: Find out who holds the lock problem Background: when using MySQL, if the traffic volume is large, there may be a large number of Locked processes, however, it is not easy to identify which SQL is causing the problem. Many people may query suspicious SQL statements through PhpMyAdmin and KILL them, however, the problem is that there may be a lot of suspicious SQL statements, so it is too clumsy to try one by one, and some people may restart MySQL in anger, but such a solution is definitely not desirable. Start the experiment. CREATE a test TABLE foo in the test database (Note: it is the MyISAM TABLE type) and add some data: create table if not exists 'foo' ('id' int (10) unsigned not null AUTO_INCREMENT, 'str' varchar (100) not null, primary key ('id') ENGINE = MyISAM; insert into 'foo' ('id ', 'str') VALUES (1, 'A'), (2, 'B'); open a MySQL Command Line terminal: mysql> USE test; mysql> SELECT SLEEP (12345) FROM foo; then open a MySQL Command Line terminal: mysql> USE test; mysql> UPDATE foo SET str = 'bar'; then execute SHOW PROCESS LIST, we can see that Locked has occurred: 10 User sleep SELECT sleep (12345) FROM foo20 Locked UPDATE foo SET str = 'bar', of course, we know that SLEEP blocks UPDATE, however, if this experiment is not used, how can we determine whether hundreds of SQL queries come into view at the same time? At this time, no one can pack the ticket, but it can only be blind. Experience is sometimes very important, but we still need to make clear the command, here is: mysqladmin debug Note: How do you not set ". my. if the cnf configuration file is used, you may need to enter the user name and password parameters for command execution, and there will be no explicit output. Do not worry. valuable items have been saved to the error log at this time: mysql> show variables like 'Log _ error'; find the specific path of the error log, open it, and view the last part of the log: 10 test. foo Locked-read Low priority read lock20 test. foo Waiting-write High priority write lock so that we can see that the SQL with id 10 blocks the SQL with id 20. As for the specific SQL, check show processlist to see it.