Long JDBC query may cause deadlock due to Borland AppServer unable to rollback the transaction

Problem:

• Product Name: Borland Application Server
• Product Version: 6.6, 6.7
• Product Component: Transaction
• Platform/OS Version: All
• JDK: All supported JDK Versions

Long JDBC query may cause deadlock due to Borland AppServer unable to rollback the transaction

Resolution:

A JDBC query runs for long time which results in timeout and Borland AppServer will try rollback the transaction. However the following scenario could happen: The rollback operation initiated by AppServer due to timeout is blocking because the Oracle JDBC driver has a resource locked. This resource locked is due to active statement on the JDBC connection. When Borland AppServer tries to rollback it does not call the cancel() operation on all statements on the connection, but it is allowing them to run to completion due to the behavior of the Oracle jdbc driver.

The following are the symptoms when such deadlock occurs:

1. High cpu usage on partition process
2. You will notice the below 2 threads (similar threads) in thread dump:

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
- locked (a oracle.jdbc.driver.T4CPreparedStatement)
- locked (a oracle.jdbc.driver.T4CConnection)
at au.gov.sa.environment.aramis.server.asset.dao.AssetSearchDAO.getAssets(AssetSearchDAO.java:603)

"Thread-59" daemon prio=10 tid=0x000000010022adf0 nid=0x5e waiting for monitor entry [0xfffffffebe6fe000..0xfffffffebe6ff628]
at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1130)
- waiting to lock (a oracle.jdbc.driver.T4CConnection)
at oracle.jdbc.OracleConnectionWrapper.rollback(OracleConnectionWrapper.java:122)
at com.inprise.visitransact.jdbc2.ConnectionProxy.rollbackWork(ConnectionProxy.java:713)
at com.inprise.visitransact.jdbc2.PooledConnectionHolder.callme(PooledConnectionHolder.java:510)
- locked (a java.lang.Object)
at com.visigenic.services.CosTransactions.TimeoutEntry.callback(Timer.java:51)
at com.visigenic.services.CosTransactions.Timer.run(Timer.java:138)

The cancel method is not invoked because the cancel method is only supported if both the DBMS and driver support aborting an SQL statement as specified in the API. In addition, we have created a test case to simulate the problem. Based on this test case, we tried to perform a cancel on the statement while the executeQuery method of the statement is still executing, however, the cancel does not seems to work.

Since the cause is due to the query taking a long time to execute, increase the query timeout to a higher value. It can be set as system properties in partition_server.config as shown below.

vmprop ITSJDBC_queryTimeout=10

This property can also be set as a pool properties for the datasource in the jndi-definition.xml. Please refer to the developer guide for more information. This property would abort the executeQuery if it exceed the timeout duration and release the lock held by it.

Author: Mathius Allo
Date : 10/04/2007