Hi,

i´m using Hibernate to access a MySQL-database. Very often i get problems with deadlocks. Searching the net for a solution i found out that for example using AUTOCOMMIT=1 and the LOCK TABLES command can cause these problems. Hibernate hides most of the database access-behaviour, so i don´t really know what happens behind the scenes.

Does anybody know what i have to do with hibernate to avoid deadlocks?
Are there certain properties that i have to set or unset?

Here is my hibernate-configuration:

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/ibn</property>
<property name="hibernate.connection.username">xxx</property>
<property name="hibernate.connection.password">xxx</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.isolation">1</property>
<property name="hibernate.jdbc.batch_size">0</property>
<property name="hibernate.order_updates">true</property>
<property name="hibernate.show_sql">false</property>
<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.c3p0.initialPoolSize">10</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_size">150</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">300</property>
<property name="hibernate.c3po.idle_test_period">3000</property>
<property name="hibernate.c3po.maxIdleTime">30</property>
<property name="hibernate.c3po.propertyCycle">300</property>
<property name="hibernate.c3po.acquireIncrement">3</property>
<property name="hibernate.c3po.testConnectionOnCheckout">false</property>
<property name="hibernate.c3po.autoCommitOnClose">false</property>
<property name="hibernate.c3po.numHelperThreads">10</property>

<property name="hibernate.connection.useCompression">true</property>
<property name="hibernate.connection.cacheResultSetMetadata">true</property>
<property name="hibernate.connection.dontTrackOpenResources">true</property>
<property name="hibernate.connection.dynamicCalendars">true</property>
<property name="hibernate.connection.zeroDateTimeBehavior">convertToNull</property>
<property name="hibernate.connection.rollbackOnPooledClose">true</property>

<property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>
</session-factory>
</hibernate-configuration>

What can i do to avoid deadlocks?

Thanks for any help.
Christoph


Hibernate version:
3.05

Full stack trace of any exception that occurs:
org.hibernate.exception.LockAcquisitionException: could not update: [de.gebit.ibn.data.IbnDatensatz#10]
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:77)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2000)
at org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1909)
at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2149)
at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
at de.gebit.ibn.utils.HibernateUtil.commitTransaction_save(HibernateUtil.java:196)
at de.gebit.ibn.data.IbnSystem.Object_speichern(IbnSystem.java:502)
at de.gebit.ibn.utils.IbnUtils.Antworten_speichern(IbnUtils.java:181)
at de.gebit.ibn.struts.actions.dataentry.user.UserInSaveEinAction.perform(UserInSaveEinAction.java:172)
at org.apache.struts.action.Action.execute(Action.java:420)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at de.gebit.ibn.filter.CompressionFilter.doFilter(CompressionFilter.java:29)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at de.gebit.ibn.filter.SessionFilter.doFilter(SessionFilter.java:66)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:307)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:385)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:748)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:678)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:871)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2921)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1570)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1085)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:670)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:104)
at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1982)
... 43 more

Name and version of the database you are using:
MySQL 4.1.12a

Hmm .. using autocommit is not exactly appropriate.
Yep, table locking can sometimes be hell.
Use appropriate app architecture: short transactions, limit the threads that use the same tables, ...