Hibernate / MySQL LockTimeoutException when using Optimistic Locking

Question

We are running the latest version of Hibernate 4 and MySQL. All of our Hibernate entities have a version field annotated with @Version since we use Optimistic Locking across the board. Nowhere in the application do we use Pessimistic Locking. My understanding was that LockTimeoutExceptions can occur when using Pessimistic Locking, however, we are getting these every so often even though we don't use this locking strategy.

In this particular scenario we have an executor service with a thread pool of size 1. When the users perform certain actions, we create notifications for other users. We delegate this notification creation to the executor service. These notifications can take some time to create so we do it this way so the user doesn't have to sit and wait for notification creation to complete. We use a single sized thread pool so that we don't have notifications being processed multiple times, etc. What we wanted was for these operations to rather queue and run sequentially. Each thread starts a single transaction, creates all the necessary notifications and the commits said transaction and closes the entity manager.

The stack trace for the exception we get is below. Any ideas why we would get a lock in a scenario such as the above? I don't understand it.

Lock wait timeout exceeded; try restarting transaction
    2015-10-08 01:27:52,195 ERROR [NotificationPublishingRunnable] : could not execute statement
    javax.persistence.LockTimeoutException: could not execute statement
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.wrapLockException(AbstractEntityManagerImpl.java:1812)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1715)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:1206)
        at za.co.bsg.ems.server.DB.merge(171bcc9:315)
        at za.co.bsg.ems.server.repositories.AbstractRepository.merge(171bcc9 ---------------:158)
        at za.co.bsg.ems.server.services.notification.EmployeeNotificationCRUDServiceSupport.createEmployeeNotification(171bcc9 remotes/origin/11_9 ---------------:97)
        at za.co.bsg.ems.server.services.notification.EmployeeNotificationCRUDServiceSupport.createEmployeeNotifications(171bcc9 remotes/origin/11_9 ---------------:75)
        at za.co.bsg.ems.server.services.notification.EmployeeNotificationCreatorSupport.createEmployeeNotifications(171bcc9 remotes/origin/11_9 -----------:80)
        at za.co.bsg.ems.server.notification.handler.NotificationHandlerManagerSupport.createNotificationsFromRequest(171bcc9 remotes/origin/11_9 ----------:233)
        at za.co.bsg.ems.server.runnable.notification.NotificationPublishingRunnable.run(171bcc9 remotes/origin/11_9 -------:64)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)
    Caused by: org.hibernate.exception.LockTimeoutException: could not execute statement
        at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:447)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211)
        at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
        at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
        at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
        at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:492)
        at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:197)
        at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:181)
        at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:216)
        at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:324)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:288)
        at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:194)
        at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
        at org.hibernate.jpa.event.internal.core.JpaMergeEventListener.saveWithGeneratedId(JpaMergeEventListener.java:73)
        at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:271)
        at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:251)
        at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:189)
        at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:85)
        at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:876)
        at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:858)
        at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:863)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.merge(AbstractEntityManagerImpl.java:1196)
        ... 10 more
    Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208

Answer 1

I ran into the same issue, and it was because I had begun a transaction and didn't commit it, and then started another transaction.

Committing that first transaction fixed the issue.

Answer 2

You annotate all your entities with @Version : This mean you want to use optimistic locking. The Optimistic locking is managed by the EntityManger, DAO or Repository.

Here you are using a preparedstatement to do your database request. So you are using a pessimistic locking which is a default features of some database. This is the reason you have the LockTimeoutException.