Re: Oracle "Set Transaction" behavior, connection pool request

On Jun 22, 2006, at 5:43 AM, Will Jaynes wrote:

> 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.

Thanks.  I've added a bug report.

It would definitely be a rollback, not a commit.

Also, I need to check when we're calling the isolation level  
setting.  We might also try to reset it at the end.  We should set it  
at the beginning.

-- Scott

>
> There is a discussion abut this problem here
> http://opensource.atlassian.com/projects/spring/browse/SPR-2090
> 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. "
> .........
>
> So I'd like to request that the Resin developers consider a change  
> to the connection pool code such as Juergen suggests.
>
> -Will
>
>