Mantis - Resin
Viewing Issue Advanced Details
1208 minor always 06-22-06 06:54 08-01-06 09:57
none 3.0.22  
0001208: Oracle connection pool state
(rep by Will Jaynes)

I want to suggest that a change to the connection pool code be considered. This change would be to accommodate a peculiarity of Oracle which can cause the connection to be left in a state which may cause an exception to be thrown the next time the connection is used.

There is a discussion abut this problem here [^]
The discussion is on the Springframework bug tracking site, but it is not strictly a Spring related problem.

Juergen Hoeller looked into the problem and determined it was related to a unique feature of Oracle. He says:
"I researched the behavior of different DBMS here: Oracle is unique here in that it considers a "SET TRANSACTION" statement (= a switch in isolation level) as the beginning of a new transaction rather than just as switch in context state. As a consequence, the resetting of the isolation level at the end of a local Spring transaction leads to some unexpected state at the Connection level... We do need to reset the isolation level in general, though, since other JDBC drivers would else use the same isolation level for the next transaction again.

A similar problem arises in other environments as well - there are corresponding issues reported for Commons DBCP and even non-Java data access libraries. The proper solution seems to be to handle this at the connection pool level: The pool has the ultimate responsibility to return Connections in proper state. It could for example call "commit" (or "rollback") on any Connection returned to the pool, which would properly reset the Oracle transaction context (this is what's usually recommended as a solution for Oracle there).

However, that said, many connection pools don't do this full cleanup of Connections... We might offer a DataSource proxy that does this on top of an existing pool, or build this option into our LazyConnectionDataSourceProxy (or the like). For the time being, I'm afraid that the only other option is to use a connection pool that does full cleanup. "

There are no notes attached to this issue.