0001208: Oracle connection pool state

Viewing Issue Simple Details [ Jump to Notes ]

[ View Advanced ] [ Issue History ] [ Print ]

Category

Severity

Reproducibility

Date Submitted

Last Update

0001208

[Resin]

minor

always

06-22-06 06:54

08-01-06 09:57

Reporter

ferg

View Status

public

Assigned To

ferg

Priority

normal

Resolution

fixed

Status

closed

Product Version

Summary

0001208: Oracle connection pool state

Description

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

Additional Information

Attached Files

Relationships

There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
06-22-06 06:54	ferg	New Issue
08-01-06 09:57	ferg	Assigned To	=> ferg
08-01-06 09:57	ferg	Status	new => closed
08-01-06 09:57	ferg	Resolution	open => fixed
08-01-06 09:57	ferg	Fixed in Version	=> 3.0.22

Mantis 1.0.0rc3[^]

26 total queries executed.
24 unique queries executed.