We are using mySQL v5, Hibernate 3.1.2 and Spring 1.2.6 in our application and are confronted with the following:
this is our hibernate.cfg.xml file:
=========================
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.autocommit">false</property>
<property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1:3306/BWB</property>
<property name="hibernate.connection.username"></property>
<property name="hibernate.connection.password"></property>
<property name="hibernate.connection.isolation">8</property>
<property name="hibernate.max_fetch_depth">2</property>
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="hibernate.order_updates">true</property>
<property name="hibernate.generate_statistics">false</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<!-- configuration pool via c3p0 -->
<property name="c3p0.acquire_increment">5</property>
<property name="c3p0.idle_test_period">300</property> <!-- seconds -->
<property name="c3p0.max_size">90</property>
<property name="c3p0.max_statements">50</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">600</property> <!-- seconds -->
<mapping resource="nl/kpn/is/bwb/dao/hibernate/A.hbm.xml" />
<mapping resource="nl/kpn/is/bwb/dao/hibernate/B.hbm.xml" />
<mapping resource="nl/kpn/is/bwb/dao/hibernate/C.hbm.xml" />
</session-factory>
</hibernate-configuration>
=========================
We have concurrent transactions that try to update 3 tables A, B and C, within one Hibernate session.
C has a many-to-one relation with B
B has a many-to-one relation with A
there is only one record in A, and every transaction eventually updates this singe record.
In our test setup, every transaction first gets for an update 1 out of 10 records from C and then its corresponding record from B (there are 2 records in B) and finally the only record from C.
After getting a record from A, we lock( ) it with LockMode.UPGRADE. The many-to-one relations are defined as:
=========================
fetch="join" cascade="save-update,lock" lazy="false"
=========================
as you can see above; isolation level is defined as SERIALIZABLE.
Despite this all we get 2 kinds of errors. if we set isolation level at 8 (serializble) we get:
=========================
10:35:04,568 WARN [JDBCExceptionReporter] SQL Error: 1213, SQLState: 40001
10:35:04,568 WARN [JDBCExceptionReporter] SQL Error: 1213, SQLState: 40001
10:35:04,583 ERROR [JDBCExceptionReporter] Deadlock found when trying to get lock; try restarting transaction
10:35:04,583 ERROR [JDBCExceptionReporter] Deadlock found when trying to get lock; try restarting transaction
10:35:04,583 ERROR [AbstractFlushingEventListener] 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:91)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:230)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:584)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:496)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:469)
at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:266)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy53.allocateStream(Unknown Source)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions.handleAllocateRequest(NAbleGatewayActions.java:60)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions$$FastClassByCGLIB$$d9a54ad1.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:698)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:51)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:643)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions$$EnhancerByCGLIB$$db9753b0.handleAllocateRequest(<generated>)
at nl.kpn.is.nable.gateway.handler.AllocateRequestHandler.executeHandle(AllocateRequestHandler.java:35)
at nl.kpn.is.nable.gateway.handler.IncomingRequestHandler.run(IncomingRequestHandler.java:60)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:647)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
... 27 more
=========================
When we inspect the locks, A has UPGRADE, B and C have READ locks.
If we don't set the isolation level, the default value 4 (repeatable read) is used. and we get:
=========================
10:46:49,438 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect): [nl.kpn.is.bwb.domain.Edge#EDGC1]
at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:1635)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2208)
at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2118)
at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2374)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:84)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:584)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:496)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:469)
at org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:266)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
at $Proxy53.allocateStream(Unknown Source)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions.handleAllocateRequest(NAbleGatewayActions.java:60)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions$$FastClassByCGLIB$$d9a54ad1.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:698)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:51)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:643)
at nl.kpn.is.bwb.web.nable.NAbleGatewayActions$$EnhancerByCGLIB$$db9753b0.handleAllocateRequest(<generated>)
at nl.kpn.is.nable.gateway.handler.AllocateRequestHandler.executeHandle(AllocateRequestHandler.java:35)
at nl.kpn.is.nable.gateway.handler.IncomingRequestHandler.run(IncomingRequestHandler.java:60)
at java.lang.Thread.run(Thread.java:595)
=========================
does someone know why this doesn't work and what can be done?
Thanks in advance.
Login
FAQ
Search