MySQL Bugs: #65468: A couple of rows in tbl had write locks that couldn't be cleared

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:	None
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

Description:
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.)

So.....

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.

oops - fix subject

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.

SHOW ENGINE INNODB STATUS output

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

SHOW FULL PROCESSLIST output

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

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.

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.

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".