0 Replies Latest reply on May 11, 2009 9:15 PM by couzteau

Occasional database dead lock

couzteau May 11, 2009 9:15 PM

Hi guys and girls,
I have a pretty straightforward webservice hosted in JBoss. I hope someone can give me a pointer or make suggestion that helps me to get a handle this issue.

I use a JNDI datasource:

<mbean code='org.jboss.resource.connectionmanager.TxConnectionManager' display-name='ConnectionManager for DataSource XedocDS' name='jboss.jca:service=LocalTxCM,name=XedocDS'>
 <attribute name='TrackConnectionByTx'>true</attribute>
 <attribute name='LocalTransactions'>true</attribute>
 <depends optional-attribute-name='ManagedConnectionPool'>
 <mbean code='org.jboss.resource.connectionmanager.JBossManagedConnectionPool' display-name='Connection Pool for DataSource XedocDS' name='jboss.jca:service=ManagedConnectionPool,name=XedocDS'>
 <depends optional-attribute-name='ManagedConnectionFactoryName'>
 <mbean code='org.jboss.resource.connectionmanager.RARDeployment' display-name='ManagedConnectionFactory for DataSource XedocDS' name='jboss.jca:service=ManagedConnectionFactory,name=XedocDS'>
 <depends optional-attribute-name='OldRarDeployment'>jboss.jca:service=RARDeployment,name='jboss-local-jdbc.rar'</depends>
 <attribute name='RARName'/>
 <attribute name='ConnectionDefinition'>javax.sql.DataSource</attribute>
 <attribute name='ManagedConnectionFactoryProperties'>
 <properties>
 <config-property name='ConnectionURL' type='java.lang.String'>jdbc:mysql://localhost:3306/xedoc2</config-property>
 <config-property name='DriverClass' type='java.lang.String'>com.mysql.jdbc.Driver</config-property>
 <config-property name='UserName' type='java.lang.String'>xedoc</config-property>
 <config-property name='Password' type='java.lang.String'>bruc3l33</config-property>
 <config-property name='CheckValidConnectionSQL' type='java.lang.String'>select * from tbl_formats</config-property>
 <config-property name='ExceptionSorterClassName' type='java.lang.String'>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</config-property>
 <config-property name='TransactionQueryTimeout' type='boolean'>true</config-property>
 <config-property name='QueryTimeout' type='int'>20</config-property>
 <config-property name='ValidateOnMatch' type='boolean'>true</config-property>
 </properties>
 </attribute>
 </mbean>
 </depends>
 <attribute name='PoolJndiName'>XedocDS</attribute>
 <attribute name='MinSize'>0</attribute>
 <attribute name='MaxSize'>20</attribute>
 <attribute name='BlockingTimeoutMillis'>30000</attribute>
 <attribute name='IdleTimeoutMinutes'>15</attribute>
 <attribute name='BackGroundValidation'>False</attribute>
 <attribute name='BackGroundValidationMinutes'>10</attribute>
 <attribute name='PreFill'>False</attribute>
 <attribute name='UseFastFail'>False</attribute>
 <attribute name='Criteria'>ByNothing</attribute>
 </mbean>
 </depends>
 <attribute name='JndiName'>XedocDS</attribute>
 <depends optional-attribute-name='CachedConnectionManager'>jboss.jca:service=CachedConnectionManager</depends>
 <depends optional-attribute-name='TransactionManagerService'>jboss:service=TransactionManager</depends>
 </mbean>

Ocassionally write requests to my db fail either with
2009-05-09 04:33:45,197 INFO [STDOUT] 2009-05-09 04:33:45,197 WARN [org.hibernate.util.JDBCExceptionReporter] - SQL Error: 1317, SQLState: 70100
2009-05-09 04:33:45,197 INFO [STDOUT] 2009-05-09 04:33:45,197 ERROR [org.hibernate.util.JDBCExceptionReporter] - Query execution was interrupted
2009-05-09 04:33:45,207 ERROR [STDERR] org.springframework.orm.hibernate3.HibernateJdbcEx ception: JDBC exception on Hibernate data access: SQLException for SQL [select distinct ...

or (but much less frequent and only if the problem persists for a long time):
SQL Error: 1205, SQLState: 41000
2009-05-09 13:09:42,917 INFO [STDOUT] 2009-05-09 13:09:42,917 ERROR [org.hibernate.util.JDBCExceptionReporter] - Lock wait timeout exceeded; try restarting transaction

In the worst case the problem persists and all write requests fail over a long period (usually until I find out, and bounce JBoss, but I have seen case where the app recovered as well after i.e a 15minute phase of consistent failure ).

In most cases it's just a single call that fails and afterwards things go back to normal. Read access always works fine. These errors started occuring after I migrated to new Hardware. At the same time I updated the mysqld version from 5.0.45 to 5.0.67

I'm not sure if there is a connection with the hardware / software upgrade though. This may also be related to more load (~1,000,000 requests a day, up from 300,000 requests a day). I noticed that occasionally I have

I use the JDBC driver 5.1.6, The app is hosted in JBoss 4.2.2GA running on Redhat 4.

I see error 1317, SQLState: 70100 increasingly often in my logfiles. This doesn't always imply subsequent write failures. But it seems that in the worst case the database locks up for good. The frequency would be in the range of 1-10 every day, unless the dead lock persists. There are probably ~10k write requests every day. So about 0.1% of write request encounter this issue.

What can I do to find out what is happening?

Restarting JBoss fixes the problem. Currently this problem limits my uptime to a few days. But the database does not need to be restarted, only JBoss.

Is it likely the the problem occurs due to lacking resources to register transactions?

I really don't know where to start with this problem, all suggestions welcome. Since I cannot repro the problem I have a hard time getting a grip on it.

Thanks

Jacques