MySQL Bugs: #71053: Lock wait timeout where there should be none

Bug #71053	Lock wait timeout where there should be none
Submitted:	2 Dec 2013 22:41	Modified:	3 Jan 2014 17:44
Reporter:	Michael Simms	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.5.32	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
A very very simple update seems to indefinitely lock a table which I cannot see a single reason it should lock for any amount of time.

Table:

CREATE TABLE `summaryUpdateTime` (
`day_change` int(11) DEFAULT NULL,
`hour_change` int(11) DEFAULT NULL,
`minute_change` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Queries

update summaryUpdateTime set minute_change=1386010620 where minute_change>=1386010560 and minute_change < 1386010620

update summaryUpdateTime set minute_change=1386010544 where minute_change > 1386010544

The table has exactly one row in it, never any more.

At the time of locking, there are 12 calls running at the same time, but these calls have been made over a period of 50 seconds (before the lock wait timeout) and so it isn't getting very heavy use, an average of one update per 4 seconds or so.

The calls to update it are made effectively at random, in a random order. The database is under high load.

innodb status doesn't show anything I can see which is interesting, it doesn't even mention the query or the table in question.

The database is being connected to in all instances via the C library.

All processes in the process list associated with this table are in the state 'Updating'

This seems to not happen in 5.5.22 but unfortunately it will be hard to find exactly which version it happens in, as this is a customer site, and we can't reproduce it on a test server, and the customer isn't going to let us just play around with their data and servers.

How to repeat:
Not reproducible. Happens on average once an hour under high load. As the load lightens, chances of it happening drop significantly.

Thank you for the report.

To troubleshoot this issue we need to know which locks were acquired at the issue time. Please turn InnoDB lock monitor to ON, wait when issue occurs one more time and send us error log file with InnoDB lock monitor output. Please also specify which transaction isolation level you use.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".