Integration

We are experiencing "hanging threads" on our production server. Currently the only way to remove these threads is to bounce the application server (jboss 4.0.5).

SNAPSHOT (currentThreadStack of a hanging thread):
java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
oracle.net.ns.Packet.receive(Unknown Source)
oracle.net.ns.DataPacket.receive(Unknown Source)
oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.net.ns.NetInputStream.read(Unknown Source)
oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1099)
oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1070)
oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:478)
oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10622)
oracle.toplink.internal.databaseaccess.DatabasePlatform.executeBatch(DatabasePlatform.java:1412)
oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:685)
oracle.toplink.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:125)
oracle.toplink.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.appendCall(ParameterizedSQLBatchWritingMechanism.java:71)
oracle.toplink.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:475)
oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:441)
oracle.toplink.publicinterface.Session.executeCall(Session.java:728)
oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:117)
oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:103)
oracle.toplink.internal.queryframework.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:238)
oracle.toplink.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:156)
oracle.toplink.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:170)
oracle.toplink.internal.queryframework.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:426)
oracle.toplink.queryframework.InsertObjectQuery.executeCommit(InsertObjectQuery.java:65)
oracle.toplink.queryframework.InsertObjectQuery.executeCommitWithChangeSet(InsertObjectQuery.java:75)
oracle.toplink.internal.queryframework.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:251)
oracle.toplink.queryframework.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:47)
oracle.toplink.queryframework.DatabaseQuery.execute(DatabaseQuery.java:620)
oracle.toplink.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:542)
oracle.toplink.queryframework.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:100)
oracle.toplink.queryframework.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:72)
oracle.toplink.publicinterface.UnitOfWork.internalExecuteQuery(UnitOfWork.java:2604)
oracle.toplink.publicinterface.Session.executeQuery(Session.java:993)
oracle.toplink.publicinterface.Session.executeQuery(Session.java:950)
oracle.toplink.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:243)
oracle.toplink.internal.sessions.CommitManager.commitAllObjectsForClassWithChangeSet(CommitManager.java:218)
oracle.toplink.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:174)
oracle.toplink.publicinterface.Session.writeAllObjectsWithChangeSet(Session.java:3193)
oracle.toplink.publicinterface.UnitOfWork.commitToDatabase(UnitOfWork.java:1293)
oracle.toplink.publicinterface.UnitOfWork.commitToDatabaseWithChangeSet(UnitOfWork.java:1389)
oracle.toplink.publicinterface.UnitOfWork.commitRootUnitOfWork(UnitOfWork.java:1137)
oracle.toplink.publicinterface.UnitOfWork.commit(UnitOfWork.java:905)


If we don't bounce the application server more and more threads will starting to hang. Eventually we will start to see another type of hanging thread. Judging from the stack trace it looks like the connection pool is filling up .

SNAPSHOT (currentThreadStack of a second type of hanging thread):
oracle.toplink.threetier.ConnectionPool.acquireConnection(ConnectionPool.java:54)
oracle.toplink.threetier.ServerSession.acquireClientConnection(ServerSession.java:240)
oracle.toplink.threetier.ClientSession.basicBeginTransaction(ClientSession.java:102)
oracle.toplink.publicinterface.Session.beginTransaction(Session.java:407)
oracle.toplink.publicinterface.UnitOfWork.beginTransaction(UnitOfWork.java:429)
oracle.toplink.publicinterface.UnitOfWork.commitToDatabase(UnitOfWork.java:1261)
oracle.toplink.publicinterface.UnitOfWork.commitToDatabaseWithChangeSet(UnitOfWork.java:1389)
oracle.toplink.publicinterface.UnitOfWork.commitRootUnitOfWork(UnitOfWork.java:1137)
oracle.toplink.publicinterface.UnitOfWork.commit(UnitOfWork.java:905)


At this point the application starts running very slowly. We learned that it's better to bounce the server before we start seeing the second type of hanging threads. If we don't bounce the server oracle will lock up.

The DBA says : Oracle doesn't run out of connections. What does occurs is that the the Oracle server locks up and prevents ssh connections from being spawned. The database server goes back to normal once we shutdown both app servers. In other words, we do not bounce the Oracle server, we do not bounce the database and yet the server goes back to normal when the Attask app servers stop using up all of it's resources.

There are no table locks and no transaction errors in the Oracle error log.

During these hanging threads I've noticed that our ReadConnectionPool that has increased significantly even under a light load. It appears that the hanging is somewhere in the read connection pool. Since the thread hangs it eats up the connection and toplink has to create a new one. Here is the code that we use to create the connection pools:

Server serverSession = project.createServerSession(minConnections, maxConnections);
ConnectionPool readPool = new ReadConnectionPool("ReadPool",
RedRockLogin.getInstance(),
minReadConnections,
maxReadConnections,
(ServerSession) serverSession);
serverSession.setReadConnectionPool(readPool);

We are also using a sequence connection pool.
SequencingControl seqCtrl = ((oracle.toplink.publicinterface.DatabaseSession)serverSession).getSequencingControl();
seqCtrl.setShouldUseSeparateConnection(true);
seqCtrl.setLogin(RedRockLogin.getSequenceLogin());



Other interesting properties:
We are running in a clustered environment with two application servers and an oracle database.
RemoteCommandManager commandMgr = new RemoteCommandManager((oracle.toplink.remotecommand.CommandProcessor) servSession);
_commandMgr.setShouldPropagateAsynchronously(true);
//we are using a topic transport manager
_commandMgr.setTransportManager(transportMgr);
// we enable distributed cache synchronization with RCM
_servSession.setShouldPropagateChanges(true);


we are setting the setCacheTransactionIsolation to SYNCRONIZED_OBJECT_LEVEL_READ_WRITE
login.setCacheTransactionIsolation(DatasourceLogin.SYNCRONIZED_OBJECT_LEVEL_READ_WRITE);

//We are using bind on paramters
login.setShouldBindAllParameters(true);
login.setShouldCacheAllStatements(true);
login.setStatementCacheSize(200);
login.setUsesStringBinding(true);

login.setUsesByteArrayBinding(true);
if (login.shouldUseByteArrayBinding()) { // Can only be used with binding.
login.setUsesStreamsForBinding(false);
}
login.setShouldForceFieldNamesToUpperCase(false);
login.setShouldOptimizeDataConversion(true);
login.setShouldTrimStrings(true);

login.setUsesNativeSQL(true);
login.setUsesBatchWriting(true);
if (login.shouldUseBatchWriting()) { // Can only be used with batch writing.
login.setUsesJDBCBatchWriting(true);
}

login.setMaxBatchWritingSize(256000);


I'm looking for some help to resolve this problem. Is something setup incorrectly? If anything is there a way I can tell Toplink to drop a connection after a given amount of time some sort of a timeout policy?

Toplink.jar
Specification-Title: TopLink

Specification-Vendor: Oracle

Specification-Version: 10.1.3.3.0

Implementation-Title: oracle.toplink

Implementation-Version: 10.1.3.3.0.070620

Implementation-Vendor: Oracle

Release-Designation: 10g Release 3

ojdbc14.jar
Oracle JDBC Driver version - "10.2.0.2.0"