Recently, while debugging through some problems related to us improperly closing result sets, I think I found a serious problem with the way that JBoss attempts to set the transaction isolation on a db connection.
Looking at the JDBC spec and the JavaDoc for the connection object, they both clearly say that one should not set the transaction isolation while inside an active transaction. (The Javadoc actually says that this leads to implementation specific behavior, but that still sounds dodgy to me)
Ok, so looking inside the XAConnectionFactory we can see that setTransactionIsolation is called inside .prepareObject whenever the connection is a XAConnectionImpl.
Looking inside ObjectPool, you can see that .prepareObject is called against the factory anytime the pool hands back a non-unique object.
Ok, so follow me here...
Client code->start ejb interceptors->random interceptors
|>TXInterceptor (Start transaction if Requires or Requires New)
|>Bean code->get database connection->setTransactionIsolation()
It seems pretty obvious to me that anytime you get a database connection from inside a bean with an active transaction, your in violation the JDBC spec. Now, this seems harmless most of the time. However, the database implementation does not in anyway have to respect the transaction isolation stated at this point. Your just getting lucky that most of the ones used with JBoss so far do.
The sane place to set it is on opening of the database connection, not on the getObject of the pool...
PS - I'm not sure if this has been discussed before BECAUSE THE FOURM SEARCH BARELY WORKS.. Please please please fix it.. It often cuts off my searchs after the first result.. Its been like this for months and months. And I know its not just me because there have been posts on this problem before.. I would search for them.. but...