hibernate Lock wait timeout exceeded;

Question

I am using Hibernate, trying to simulate 2 concurrent update to the same row in database.

Edit: I moved em1.getTransaction().commit to be right after em1.flush(); I am not getting any StaleObjectException, the two transactions committed successfully.

Session em1=Manager.sessionFactory.openSession();
Session em2=Manager.sessionFactory.openSession();

em1.getTransaction().begin();
em2.getTransaction().begin();

UserAccount c1 = (UserAccount)em1.get( UserAccount.class, "root" );
UserAccount c2 = (UserAccount)em2.get( UserAccount.class, "root" );

c1.setBalance( c1.getBalance() -1 );
em1.flush();
System.out.println("balance1 is "+c2.getBalance());
c2.setBalance( c2.getBalance() -1 );
em2.flush(); // fail

em1.getTransaction().commit();
em2.getTransaction().commit();

System.out.println("balance2 is "+c2.getBalance());

I getting the following exception on em2.flush(). Why?

2009-12-23 21:48:37,648  WARN JDBCExceptionReporter:100 - SQL Error: 1205, SQLState: 41000
2009-12-23 21:48:37,649 ERROR JDBCExceptionReporter:101 - Lock wait timeout exceeded; try restarting transaction
2009-12-23 21:48:37,650 ERROR AbstractFlushingEventListener:324 - Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.persister.entity.AbstractEntityPersister.processGeneratedProperties(AbstractEntityPersister.java:3702)
    at org.hibernate.persister.entity.AbstractEntityPersister.processUpdateGeneratedProperties(AbstractEntityPersister.java:3691)
    at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:147)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)
    at com.ch.whoisserver.test.StressTest.main(StressTest.java:54)
Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1213)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:912)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
    ... 10 more

score 22 · Accepted Answer · edited Mar 10 '12 at 23:47

22

Well, you're trying to get into a deadlock and you're succeeding :-)

Transaction1 starts, updates (and locks) row with your entity.
Transaction2 tries to do the same but can't because the row is still locked. So it waits (and waits, and waits) until timeout is exceeded

Real life simulation would have 1st and 2nd entity manager plus appropriate updates / transactions in separate threads. That way you'd have:

Transaction1 starts, updates (and locks) row with your entity.
Transaction2 tries to do the same but can't because the row is still locked. So it waits (and waits, and waits) ...
Meanwhile Transaction1 is committed and lock is released
Transaction2 can now proceed

Note that at that point (#4 above) you'd be overwriting changes made by Transaction1. Hibernate can use optimistic locking as well as pessimistic locking to prevent that from happening.

Update (based on comment):

If the entity is versioned, Transaction2 (#4 above) will fail. However, your code as posted does not get to that point because Transaction2 can't obtain the lock as explained above. If you want to specifically test that optimistic version control is working you can do the following:

Obtain em1, start transaction, get your entity, commit transaction, close em1.
Obtain em2, start transaction, get your entity, update your entity, commit transaction, close em2.
Obtain em3, start transaction, attempt to update entity you've loaded in step 1 - test should fail here.

edited Mar 10 '12 at 23:47

Lukasz Stelmach

5,281
4
25
29

answered Dec 24 '09 at 07:21

ChssPly76

99,456
24
206
195

I am actually trying to write a test case to see if optimistic locking works,the object UserAccount in question is using version, please see this question for detail http://stackoverflow.com/questions/1938671/concurrency-in-hibernate , in this case with two threads would transaction2 get a staledObjectException to detect a change in underlying data? – user217631 Dec 24 '09 at 07:40
I've updated my answer above - using two threads is not a good way to test **specifically** optimistic locking (due to being unpredictable) – ChssPly76 Dec 24 '09 at 07:50
in your step2, do you mean obtain em2 instead of obtain em1? – user217631 Dec 24 '09 at 08:05
Moving em1.commit after em1.flush should be enough I think. – ewernli Dec 24 '09 at 09:11
Yes, sorry. All 3 steps should have 3 different entity managers. – ChssPly76 Dec 24 '09 at 17:00
ewernli, I moved em1.commit after em1.flush, both updated successfully, optimistic locking is not working. – user217631 Dec 25 '09 at 00:22
I get this exception even when I am running single update at a time. There is no transaction that is running before or parallel to it. What could be the reason for it ? – Suhail Gupta Apr 27 '14 at 13:14

hibernate Lock wait timeout exceeded;

1 Answers1

Linked