Java Database Connectivity (JDBC)

I've encountered a deadlock situation between one of my application threads and the Java Finalizer thread.
I know this was a problem with ResultSets and Statements in JDBC, and I've performed a complete code scrub on
usage of those.

But now I think it's occurring due to the ConnectionContext within a java class generated from SQLJ(which represents
an OracleObject Type). Seems as though the Finalizer thread calls the finalize() method on the
ConnectionContext object within the Oracle Object Type java class. This in turn attempts to clean up the connection
stored within the ConnectionContext, which is causing the deadlock with my application thread.

Anyone have any ideas that might help me out?

I've included the thread deadlock dump.

SIGQUIT
A SIGQUIT has been received. Do you want to:
[ 0 ] continue program
[ 1 ] check & print one deadlock
[ 2 ] check & print all deadlocks
[ 3 ] dump thread stacks
[ 4 ] dump lock registry
[ 5 ] heap inspection
[ 6 ] terminate program
Type number corresponding to selected action:
FOUND A JAVA LEVEL DEADLOCK:
---------------------------
t@82 waiting to lock object@0xe9eafbd0:"oracle/jdbc/driver/OracleCallableStatement"
which is locked by t@6
t@6 waiting to lock object@0xfec41188:"oracle/jdbc/driver/OracleConnection"
which is locked by t@82

JAVA STACK INFORMATION FOR THREADS LISTED ABOVE:
-----------------------------------------------

Java Stack for t@82 (EE = 0x119ec00) "RMI TCP Connection(23)-NGSN-6308.wcomnet.com/166.37.73.22" :
==========
[1] oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatement.java:242)
[2] oracle.jdbc.driver.OracleConnection.close_statements(OracleConnection.java:1142)
[3] oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:529)
[4] ngsn.gap.dbwrapper.ConnectionPool.pushConnection(Unknown Source)
[5] ngsn.gap.dbwrapper.DbWrapper.close(Unknown Source)
[6] ngsn.gap.dbwrapper.DbCallProfile.cpvSequenceOfActiveVersion(Unknown Source)
[7] ngsn.gap.dbwrapper.DbCallProfile.xlateOracleToGap(Unknown Source)
[8] ngsn.gap.dbwrapper.DbCallProfile.getDbCallProfileSys(Unknown Source)
[9] ngsn.gap.dbwrapper.DbCallProfile.ldbIdForTransferLdb(Unknown Source)
[10] ngsn.gap.dbwrapper.DbWrapper.ldbIdForTransferLdb(Unknown Source)
[11] ngsn.gap.orderprocessing.OrderProcessing.setLdbIdForTransferLdb(Unknown Source)
[12] ngsn.gap.orderprocessing.OrderProcessing.postProcess(Unknown Source)
[13] ngsn.gap.orderprocessing.RpOrderProcessing.postProcess(Unknown Source)
[14] ngsn.gap.orderprocessing.OrderProcessing.process(Unknown Source)
[15] ngsn.gap.orderprocessing.OrderOrderProcessing.process(Unknown Source)
[16] ngsn.gap.servlet.GapRmiServlet.performOrderProcessing(Unknown Source)
[17] ngsn.gap.servlet.GapRmiServlet.setOrder(Unknown Source)
[18] java.lang.reflect.Method.invoke(Native Method)
[19] sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:179)
[20] sun.rmi.transport.Transport$1.run(Transport.java)
[21] java.security.AccessController.doPrivileged(Native Method)
[22] sun.rmi.transport.Transport.serviceCall(Transport.java:96)
[23] sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:421)
[24] sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:518)
[25] java.lang.Thread.run(Thread.java:484)

Java Stack for t@6 (EE = 0x1a48a8) "Finalizer" :
==========
[1] oracle.jdbc.driver.OracleConnection.remove_statement(OracleConnection.java:1131)
[2] oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:435)
[3] oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatement.java:242)
[4] sqlj.runtime.profile.ref.RTStatementJDBCBase.releaseStatement(RTStatementJDBCBase.java:709)
[5] sqlj.runtime.profile.ref.CachedStatementWrapper.releaseStatement(CachedStatementWrapper.java:80)
[6] sqlj.runtime.profile.ref.CachedStatementWrapper.releaseStatement(CachedStatementWrapper.java:80)
[7] sqlj.runtime.profile.ref.StatementCacheProfile$DataNode.releaseStatement(StatementCacheProfile.java:314)
[8] sqlj.runtime.profile.ref.StatementCacheProfile.close(StatementCacheProfile.java:109)
[9] sqlj.runtime.ref.ProfileGroup$ConnectedGroup.close(ProfileGroup.java:166)
[10] sqlj.runtime.ref.ConnectionContextImpl.close(ConnectionContextImpl.java:235)
[11] sqlj.runtime.ref.ConnectionContextImpl.finalize(ConnectionContextImpl.java:264)
[12] java.lang.ref.Finalizer.runFinalizer(Finalizer.java:84)
[13] java.lang.ref.Finalizer.access$1(Finalizer.java:83)
[14] java.lang.ref.Finalizer$FinalizerThread.run(Finalizer.java:175)
-----------------------------------------------
Found 1 deadlock

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

From TechNet, SQLJ and JDBC Interoperability...

Closing Shared Connections

When you get a JDBC connection instance from a SQLJ connection context instance (using the getConnection() method)
or you create a SQLJ connection context instance from a JDBC connection instance (using the connection context
constructor), you must close only the connection context instance. By default, calling the close() method of a
connection context instance closes the associated JDBC connection instance and the underlying database connection,
thereby freeing all resources associated with the connection.

Note, however, that closing the JDBC connection instance will not close the associated SQLJ connection
context instance. The underlying database connection would be closed, but the resources of the connection
context instance would not be freed until garbage collection.

If you want to close a SQLJ connection context instance without closing the associated JDBC connection
instance (if, for example, the Connection instance is being used elsewhere, either directly or by another
connection context instance), then you can specify the boolean constant KEEP_CONNECTION to the close() method,
as follows (presume you have been using a connection context instance ctx):

ctx.close(ConnectionContext.KEEP_CONNECTION);

If you do not specify KEEP_CONNECTION, then the associated JDBC connection instance is closed by default.
You can also specify this explicitly:

ctx.close(ConnectionContext.CLOSE_CONNECTION);

KEEP_CONNECTION and CLOSE_CONNECTION are static constants of the sqlj.runtime.ConnectionContext interface.

If you do not explicitly close a connection context instance, then it will be closed by the finalizer
during garbage collection with KEEP_CONNECTION, meaning the resources of the JDBC connection instance
would not be freed until released explicitly or by garbage collection.


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
So based on the above information, I added the below method to all of
the oracle objects we have.

It basically calls close() on the ConnectionContext that is within the oracle object,
therefore based on my understanding, should close the connection context without closing the associated
JDBC connection, which I still need for transactional purposes.

Do you think this may be broken? If so, I guess I'd have to rewrite everything in SQL statements,
or try to get a workaround somehow.
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


Code snippet from Ordertype.java:

public void close() throws SQLException {
cat.debug( "Connection in Ordertype object: " + _ctx.getConnection());
ctx.close( ConnectionContext.KEEPCONNECTION);
cat.debug( "ConnectionCtx closed: " + _ctx.isClosed());
}



Corresponding output in log file:

2002-06-27 15:36:46,762 DEBUG ngsn.gap.obj.Ordertype - Connection in Ordertype object: oracle.jdbc.driver.OracleConnection@1418f12
2002-06-27 15:36:46,762 DEBUG ngsn.gap.obj.Ordertype - ConnectionCtx closed: true