Mysql – InnoDB table locks without apparent reason

innodbMySQL

I have an InnoDB table for visitors' counting, which has perfectly worked for several years, but it failed twice yesterday, may be cause of the increase of visitors
Without apparent reason, this table locked, with hundreds of DELETE an REPLACE INTO queries (+500) with "updating" or "cleaning up" status. (I've no more the copy of the processlist…)

This table contains few entries, between 500 and 1500, so the updating queries are usualy very fast and don't lock.

When the problem occurred, I checked the processlist a lot of times, and I haven't seen any query which was locking the table explicitly. Actually, I set my websites in maintenance mode, so no more queries were added to the queue, and there still were more than 500 queries, all the same :

+---------+---------+--------------+---------+---------+------+----------+-----------------------------------------------+
| Id      | User    | Host         | db      | Command | Time | State    | Info                                          |
+---------+---------+--------------+---------+---------+------+----------+-----------------------------------------------+
| 2780657 | XXXXXXX | XXXXXX:58199 | XXXXXXX | Query   |  660 | updating | DELETE FROM connected WHERE time < 1272100968 |
| 2764941 | XXXXXXX | XXXXXX:57314 | XXXXXXX | Query   |  660 | updating | DELETE FROM connected WHERE time < 1272100968 |
| 2784467 | XXXXXXX | XXXXXX:44221 | XXXXXXX | Query   |  661 | updating | DELETE FROM connected WHERE time < 1272100969 |
...

I don't know where I must search to find the cause of this problem and resolve it definitively.

But I guess this could give you a better vision of the problem :

mysql> show global status like "%innodb_row_lock%";
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| Innodb_row_lock_current_waits | 0         |
| Innodb_row_lock_time          | 132004175 |
| Innodb_row_lock_time_avg      | 10521     |
| Innodb_row_lock_time_max      | 59373     |
| Innodb_row_lock_waits         | 12546     |
+-------------------------------+-----------+
5 rows in set (0.00 sec)

Sorry for my poor english, and thanks for your help 😉

Best Answer

The trick is usually to locate the longest running query, which is identified in show full processlist; You can also use a utility like mytop to sort the queries by runtime.

Once you isolate the query causing the lock, you can try to determine why it locked. Was there an alteration on the table causing a write lock? There are a variety of different scenarios, which in most cases will be related to an application issue.

It's very difficult to provide any definitive recommendation without actively troubleshooting. At the very least, further details such as the processlist and the application in question would be helpful.

Related Topic