Hi all,

I need your help. I have pretty hudge project based on hibernate, and my problem is, that after few hours/days sometimes only minutes all threads are waiting for connect to database, and server needs restart. I think problem is for sure in some deadlock in database, because after restarting of application server the problem persisted. I tried ti restart also mysql server and problem wasn't solved. Only restart of physical machine always help.

typical stacktrace from deadlocked thread is:

at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
- locked <0x16c8c8e> (a com.mysql.jdbc.util.ReadAheadInputStream)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1931)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1300)
- locked <0x17fd0cd> (a java.lang.Object)
at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:307)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4363)
- locked <0x65590a> (a com.mysql.jdbc.Connection)
at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4301)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at server.modules.games.defaults.playing.ServerGameRepository.forceUserArchiveOldGames(ServerGameRepository.java:296)
at server.modules.games.defaults.playing.ServerGameManager.forceUserArchiveOldGames(ServerGameManager.java:453)
at server.modules.games.defaults.playing.GameModuleObserver.analyzeMessage(GameModuleObserver.java:134)
at server.modules.ModuleObserver.update(ModuleObserver.java:36)
at server.modules.ServerCoreContainer.notifyListeners(ServerCoreContainer.java:78)
at server.modules.ServerCoreContainer.sendMessage(ServerCoreContainer.java:33)
at server.multithread.NetworkTask.call(NetworkTask.java:47)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:268)
at java.util.concurrent.FutureTask.run(FutureTask.java:54)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)


here is my configuration from spring bean

<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</prop>
<prop key="hibernate.connection.pool_size">5</prop>
<prop key="hibernate.connection.useUnicode">true</prop>
<prop key="hibernate.connection.characterEncoding">UTF-8</prop>
<prop key="hibernate.current_session_context_class">thread</prop>
<prop key="hibernate.query.substitutions">true=1 false=0</prop>
<prop key="hibernate.use_outer_join">false</prop>

<prop key="hibernate.connection.autocommit">false</prop>
<prop key="hibernate.connection.autoReconnect">true</prop>
<prop key="hibernate.connection.autoReconnectForPools">true</prop>


<prop key="hibernate.bytecode.provider">javassist</prop>
<prop key="hibernate.cache.use_second_level_cache">false</prop>

<prop key="hibernate.transaction.manager_class">org.springframework.orm.hibernate3.HibernateTransactionManager</prop>

</props>
</property>


pls, help my, I am completly lost. Is the problem problem of my code(i have threadpool which calls polled database connections)? Shall I writelock all access to database to prevent this? shall I writelock only per session? or is this connected with some bad configuration, or wrong drivers? Database is on the same machine that app server, so it can't be because of lost connectivity. It just works for some time and than deadlock..


thanks for any help

Libor

HI we solved this problem in our project using c3p0 properties........

in beans.xml


<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test" />
<property name="user" value="root" />
<property name="password" value="test" />

<!-- c3p0 Connection Pool Properties -->
<property name="initialPoolSize" value="10" />
<property name="minPoolSize" value="5" />
<property name="maxPoolSize" value="25" />
<property name="acquireRetryAttempts" value="10" />
<property name="acquireIncrement" value="5" />
<property name="idleConnectionTestPeriod" value="3600" />
<property name="maxIdleTime" value="19800" />
<property name="maxConnectionAge" value="21600" />
<property name="preferredTestQuery" value="SELECT 1" />
<property name="testConnectionOnCheckin" value="false" />
</bean>




use c3p0 properties.
if solved this problem using these properties..then plz acknowledge me.thanks

rate me if possible..

Hi, thx for reply.

I moved to c3p0, but it didn't solve the problem. Anyway thx, iIam gonna to use c3p0 from now. I found out, that a deadlock it is connected with one select, which is for some reason written in HQL and not in criteria. It is select throughts many tables, something like

String select = (p_isCount ? "select count(p) " : "select p" )+" from Game p " +
"inner join p.gamePlayerArray q " +
"left join "+fetch+" q.gameUser gu " +
"left join "+fetch+" gu.eloSimpleList e " +
"left join "+fetch+" gu.userData u " +
"inner join p.gamePlayerArray q1 " +
"left join "+fetch+" q1.gameUser gu1 " +
"left join "+fetch+" gu1.eloSimpleList e1 " +
"left join "+fetch+" gu1.userData u1 " +
"where (p.isLongTermGame = true ) and (p.result = '-1') and (index(q) = 0 and index(q1) = 1) and (q.id <> q1.id) " +
"and (gu.gameUserID <> gu1.gameUserID ) and (u.userID <> u1.userID) " +
"and ( index(e) = 1 and index(e1) = 1) and (e.id <> e1.id) ";

+ many other conditions.

And this select works on my home environment, but on linux server, it does not. In my opinion, there must be some bad driver on server mysql (Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (x86_64) using readline 5.2), which causes deadlock. in fact, this select is slow too much, so i have to find out how to rewrite it. But for now, I need help. Is it database problem? is it possible hibernate configuration problem? what should i do. thx.