MySQL Bugs: #65153: InnoDB locking issues after mysql crash recovery

Bug #65153	InnoDB locking issues after mysql crash recovery
Submitted:	30 Apr 2012 6:26	Modified:	30 May 2012 6:51
Reporter:	Vishal Sadhnani	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.45	OS:	Linux (Fedora)
Assigned to:		CPU Architecture:	Any
Tags:	Innodb locking

Description:
We are using Quartz schedule from Java with MySQL 5.1.45 version DB. In a lot of instances(4-5 in last 2 years) we have observed that after system hard reboot, we are observing locking issues with DB. Here is the exception from the logs :

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Lock wait timeout exceeded; try restarting transaction See nested exception: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:112)
at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:112)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3740)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3709)

Seems Quartz is unable to obtain locks on MySQL tables after crash recovery. The tables are InnoDB tables.

How to repeat:
Issue is observed only after System was not rebooted in a clean way & mysql recovered after that.

Do you have any XA transactions that need to be recovered?  Please catch output next time:

XA RECOVER;
SHOW ENGINE INNODB STATUS\G

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