Locked query explosion

MySQL & MariaDB Other MySQL® Questions
1

I was greeted by this wonderful output from one of my DB servers:

mysql> show full processlist;±---------±----------±-----------------±-----±--------±-----±-------±-------------------------------------+| Id | User | Host | db | Command | Time | State | Info |±---------±----------±-----------------±-----±--------±-----±-------±-------------------------------------+| 21180274 | boarduser | 10.0.0.102:59802 | abc2 | Query | 853 | NULL | LOCK TABLES x WRITE || 21180415 | boarduser | 10.0.0.102:51423 | abc2 | Query | 847 | NULL | LOCK TABLES x WRITE || 21180809 | boarduser | 10.0.0.100:58242 | abc2 | Query | 0 | NULL | show full processlist || 21181068 | boarduser | 10.0.0.102:60884 | abc2 | Query | 820 | NULL | LOCK TABLES x WRITE || 21181610 | boarduser | 10.0.0.102:49327 | abc2 | Query | 797 | NULL | LOCK TABLES x WRITE || 21182156 | boarduser | 10.0.0.102:49332 | abc2 | Query | 774 | NULL | LOCK TABLES x WRITE || 21183134 | boarduser | 10.0.0.102:63748 | abc2 | Query | 733 | Locked | LOCK TABLES x READ || 21183365 | boarduser | 10.0.0.102:63776 | abc2 | Query | 723 | NULL | LOCK TABLES x WRITE || 21184205 | boarduser | 10.0.0.102:63957 | abc2 | Query | 691 | Locked | LOCK TABLES x READ || 21184223 | boarduser | 10.0.0.102:63959 | abc2 | Query | 690 | Locked | LOCK TABLES x READ || 21185017 | boarduser | 10.0.0.102:64114 | abc2 | Query | 663 | NULL | LOCK TABLES x WRITE || 21185417 | boarduser | 10.0.0.102:64200 | abc2 | Query | 647 | Locked | LOCK TABLES x READ || 21185784 | boarduser | 10.0.0.102:64270 | abc2 | Query | 633 | NULL | LOCK TABLES x WRITE || 21186488 | boarduser | 10.0.0.102:64423 | abc2 | Query | 605 | Locked | LOCK TABLES x READ || 21186684 | boarduser | 10.0.0.102:64479 | abc2 | Query | 598 | Locked | LOCK TABLES x READ || 21187368 | boarduser | 10.0.0.102:64612 | abc2 | Query | 570 | Locked | LOCK TABLES x READ || 21188628 | boarduser | 10.0.0.102:64922 | abc2 | Query | 519 | Locked | LOCK TABLES x READ || 21189640 | boarduser | 10.0.0.102:65084 | abc2 | Query | 480 | Locked | LOCK TABLES x READ || 21189958 | boarduser | 10.0.0.102:49824 | abc2 | Query | 466 | Locked | LOCK TABLES x READ || 21190054 | boarduser | 10.0.0.102:60472 | abc2 | Query | 462 | Locked | LOCK TABLES x READ || 21190414 | boarduser | 10.0.0.102:64882 | abc2 | Query | 449 | Locked | LOCK TABLES x READ || 21191849 | boarduser | 10.0.0.102:52529 | abc2 | Query | 389 | Locked | LOCK TABLES x READ || 21192966 | boarduser | 10.0.0.102:53159 | abc2 | Query | 343 | Locked | LOCK TABLES x READ || 21193122 | boarduser | 10.0.0.102:53184 | abc2 | Query | 337 | Locked | LOCK TABLES x READ || 21193868 | boarduser | 10.0.0.102:53315 | abc2 | Query | 306 | Locked | LOCK TABLES x READ || 21194730 | boarduser | 10.0.0.102:53399 | abc2 | Query | 272 | Locked | LOCK TABLES x READ || 21195061 | boarduser | 10.0.0.102:55693 | abc2 | Query | 259 | Locked | LOCK TABLES x READ || 21195930 | boarduser | 10.0.0.102:55821 | abc2 | Query | 224 | Locked | LOCK TABLES x READ || 21196348 | boarduser | 10.0.0.102:60491 | abc2 | Query | 208 | Locked | LOCK TABLES x READ || 21196694 | boarduser | 10.0.0.102:65380 | abc2 | Query | 194 | Locked | LOCK TABLES x READ || 21197674 | boarduser | 10.0.0.102:51113 | abc2 | Query | 156 | Locked | LOCK TABLES x READ || 21198024 | boarduser | 10.0.0.102:51182 | abc2 | Query | 142 | Locked | LOCK TABLES x READ || 21198387 | boarduser | 10.0.0.102:60036 | abc2 | Query | 129 | Locked | LOCK TABLES x READ || 21199468 | boarduser | 10.0.0.102:61690 | abc2 | Query | 86 | Locked | LOCK TABLES x READ || 21199529 | boarduser | 10.0.0.102:61697 | abc2 | Query | 84 | Locked | LOCK TABLES x READ || 21200066 | boarduser | 10.0.0.102:61809 | abc2 | Query | 60 | Locked | LOCK TABLES x READ || 21200497 | boarduser | 10.0.0.102:61909 | abc2 | Query | 41 | Locked | LOCK TABLES x READ || 21200777 | boarduser | 10.0.0.102:61966 | abc2 | Sleep | 0 | | NULL || 21200782 | boarduser | 10.0.0.102:61968 | abc2 | Query | 31 | Locked | LOCK TABLES x WRITE || 21200806 | boarduser | 10.0.0.102:61971 | abc2 | Query | 29 | Locked | LOCK TABLES x WRITE || 21200819 | boarduser | 10.0.0.102:61973 | abc2 | Query | 29 | Locked | LOCK TABLES x WRITE || 21200830 | boarduser | 10.0.0.102:61975 | abc2 | Query | 29 | Locked | LOCK TABLES x WRITE || 21200833 | boarduser | 10.0.0.102:61977 | abc2 | Query | 28 | Locked | LOCK TABLES x WRITE || 21200857 | boarduser | 10.0.0.102:61982 | abc2 | Query | 28 | Locked | LOCK TABLES x WRITE || 21200864 | boarduser | 10.0.0.102:61984 | abc2 | Query | 27 | Locked | LOCK TABLES x WRITE || 21200886 | boarduser | 10.0.0.102:61988 | abc2 | Query | 26 | Locked | LOCK TABLES x WRITE || 21200898 | boarduser | 10.0.0.102:61990 | abc2 | Query | 26 | Locked | LOCK TABLES x WRITE || 21200900 | boarduser | 10.0.0.102:61992 | abc2 | Query | 26 | Locked | LOCK TABLES x WRITE || 21200919 | boarduser | 10.0.0.102:61997 | abc2 | Query | 25 | Locked | LOCK TABLES x WRITE || 21200942 | boarduser | 10.0.0.102:61999 | abc2 | Query | 25 | Locked | LOCK TABLES x WRITE || 21200987 | boarduser | 10.0.0.102:62006 | abc2 | Query | 23 | Locked | LOCK TABLES x WRITE || 21200993 | boarduser | 10.0.0.102:62008 | abc2 | Query | 22 | Locked | LOCK TABLES x WRITE || 21201034 | boarduser | 10.0.0.102:62012 | abc2 | Query | 21 | Locked | LOCK TABLES x WRITE || 21201043 | boarduser | 10.0.0.102:62014 | abc2 | Query | 20 | Locked | LOCK TABLES x WRITE || 21201046 | boarduser | 10.0.0.102:62016 | abc2 | Query | 20 | Locked | LOCK TABLES x WRITE || 21201089 | boarduser | 10.0.0.102:62024 | abc2 | Query | 18 | Locked | LOCK TABLES x WRITE || 21201113 | boarduser | 10.0.0.102:62028 | abc2 | Query | 17 | Locked | LOCK TABLES x WRITE || 21201156 | boarduser | 10.0.0.102:62033 | abc2 | Query | 15 | Locked | LOCK TABLES x WRITE || 21201188 | boarduser | 10.0.0.102:58944 | abc2 | Query | 14 | Locked | LOCK TABLES x WRITE || 21201289 | boarduser | 10.0.0.102:59252 | abc2 | Query | 9 | Locked | LOCK TABLES x WRITE || 21201313 | boarduser | 10.0.0.102:56240 | abc2 | Query | 8 | Locked | LOCK TABLES x WRITE || 21201400 | boarduser | 10.0.0.102:51523 | abc2 | Query | 3 | Locked | LOCK TABLES x WRITE || 21201413 | boarduser | 10.0.0.102:50122 | abc2 | Query | 2 | Locked | LOCK TABLES x WRITE || 21201429 | boarduser | 10.0.0.102:59458 | abc2 | Query | 2 | Locked | LOCK TABLES x WRITE || 21201453 | boarduser | 10.0.0.102:64592 | abc2 | Query | 2 | Locked | LOCK TABLES x WRITE || 21201463 | boarduser | 10.0.0.102:52391 | abc2 | Query | 1 | Locked | LOCK TABLES x WRITE || 21201491 | boarduser | 10.0.0.102:54131 | abc2 | Query | 0 | Locked | LOCK TABLES x WRITE || 21201506 | boarduser | 10.0.0.102:62358 | abc2 | Sleep | 0 | | NULL |±---------±----------±-----------------±-----±--------±-----±-------±-------------------------------------+69 rows in set (0.01 sec)

As you can imagine, it gets worse until all the connections are filled.
Table x is MyISAM. There are about 100 requests per minute to a PHP script which does LOCK TABLES x WRITE as shown above, does some very simple select queries (no joins, mostly uses covering indexes) and a single row update and insert on a table with <5000 rows, and unlocks and disconnects to do some more work. I am baffled as to why these locked queries are piling up and how to fix it. Shouldn’t there be a query that these locked threads are waiting upon? I don’t know if InnoDB would fix this, since I will end up selecting every row in the table for update anyway.

Any ideas? Thanks

2

There is but it might be hard to catch

It may well be some query in the Sleep stage which just took the table and now sleeping with the lock or it could be some long running query which uses this table (these are easy to catch)

One of the frequent reasons for this problem is using persistent connection when script is aborted after locking the table and it could take a while before some other script runs unlock tables in the same connection to set table free.

3

Hi Peter,
Thanks for your reply. We definitely have no scripts using persistent connections, and queries taking more than 1 second are extremely rare (verified with the slow query log). Any advice on how to track down what’s causing this?

4

Which MySQL version is that ? What OS ?

Also try running “mysqladmin debug” which will post you some data about who owns table locks.

When this happens try killing all connections which are not waiting for lock tables as this would release the lock

It is possible some connection lock the table and when does long batch job but no long queries…

It also could be spme form of threading or MySQL bug with lock beomming stale