You could try deploying your app on jboss 4 where the default behavior is now to violate the jca spec and close any connections you leave open, with a loud warning.
I set-up a JDBC driver (mimer jtrace) that logs all the connects and statements done through JDBC. This allows me to see that the last transaction done with each connections follows the same pattern (same code):
call to SLSB A (start transaction)
SLSB A calls some EB's (resulting only in selects)
SLSB A calls SLSB B that does an insert
SLSB A calls a Java object that calls some EB's (resulting only in selects)
SLSB A calls a Java object that calls SLSB B that does an update of same record
call to SLSB A returns (commit)
After the commit, the connection is never reused.
I can say that all ResultSet and (Prepared)Statement are closed properly. I see it also from the JDBC log.
Should I search for a lock contention in my DB (Oracle 8.1.7), for error in my app or a configuration or other problem in JBoss 3?
I have the same problem. Did you figure out how to solve it?
Start by trying 3.0.7 (currently from cvs), 3.2RC2 or RC3(RC3 currently from cvs) or 4 (if you want to live dangerously) and see if JBoss thinks you are not closing any connections. So far these problems have always turned out to be from unclosed connections, no matter what other tracing was used.