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());

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

Answer 1

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

1. Transaction1 starts, updates (and locks) row with your entity.
2. 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:

1. Transaction1 starts, updates (and locks) row with your entity.
2. Transaction2 tries to do the same but can't because the row is still locked. So it waits (and waits, and waits) ...
3. Meanwhile Transaction1 is committed and lock is released
4. 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:

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