Bug #65468 A couple of rows in tbl had write locks that couldn't be cleared
Submitted: 30 May 2012 20:48 Modified: 28 Feb 2013 19:00
Reporter: Greg Kemnitz Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.5.14-enterprise-commercial-advanced-lo OS:Solaris (innodb)
Assigned to: CPU Architecture:Any
Tags: foreign key, innodb, write lock

[30 May 2012 20:48] Greg Kemnitz
We had the following situation arise on our production server.  We support a couple hundred enterprise customers on this server, so it's fairly active.

The situation was the following:

1.  Somehow, two rows in our "users" table (about 1000 rows) ended up with write locks.  Attempting to update the rows through our app or from the MySQL command line resulted in hangs.  (This effectively meant these users couldn't log in to our system.)  

2.  SHOW ENGINE INNODB STATUS showed that the UPDATE query was waiting on a lock if it was being executed, but if no query was active (ie, we'd killed it), no locks showed up.  When we killed the query from the command line, we saw the error message that looks like Bug 64456.  Note that reads of the locked records did work.

3.  We thought that maybe a connection had a dangling transaction that was holding a lock, so we refreshed all database connections.  This didn't free the locks.

4.  To get around this problem, we did the following:

o  Shut down all our apps that touch the user table.
o  Dropped all foreign keys pointing at the user table.
o  Copied the user table to a work table.
o  Renamed the user table to a scratch name.
o  Renamed the work table to the user table.
o  Rebuilt the foreign keys.
o  Restarted the apps.  After this, recs unstuck, everything worked.

(Note that it is likely that bouncing the db would have worked to clear the lock, but bouncing the db in the middle of the week would have been a huge operational problem for our customers.  Also, we weren't sure whether this was really a lock problem or some sort of data corruption.)

5.  After this, we tried to update the recs in the "old" (renamed) user table, and the updates now worked.  This was many hours after the problem was first noticed.  Unfortunately, we didn't try this after dropping the foreign keys.  (I guess that the fact that the rename itself didn't hang means the foreign keys going away got rid of the lock.)


We think there is possibly a server or storage engine bug here relating to foreign keys.

More info:

1.  There is nothing helpful in mysqld.log or slow-query.log
2.  The server was last rebooted about a month ago.
3.  If it help, we're using Solaris i86pc "SunOS 5.1"
4.  we're using connector 5.1.10.

How to repeat:
Unfortunately, we don't have a clue.  Our app has run for about eight months using 5.5.14 without seeing this situation develop.
[30 May 2012 20:49] Greg Kemnitz
oops - fix subject
[31 May 2012 9:45] Valeriy Kravchuk
Check that bug you referenced, probably number is wrong (it's a Workbench bug).

If you have the output of SHOW ENGINE INNODB STATUS and any other information from the moment when you had the problem, please, upload it to this bug report.
[31 May 2012 18:52] Greg Kemnitz

Attachment: innodb_out.txt (text/plain), 27.08 KiB.

[31 May 2012 18:53] Greg Kemnitz

Attachment: show_full_processlist.txt (text/plain), 10.91 KiB.

[31 May 2012 18:56] Greg Kemnitz
You're right that the bug number above is wrong.  The right bug number for the error we saw when we killed the updates was Bug 64556.
[30 Jan 2013 19:00] Sveta Smirnova
Thank you for the feedback.

Please try with current version 5.5.29 and if issue is repeatable provide us output of SHOW CREATE TABLE for table ga_ra_user and all, linked with foreign keys.
[1 Mar 2013 1:00] Bugs System
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".