Transaction behaviour after closed connection?
200424Jan 29 2010 — edited Jan 29 2010Hi,
Using JDBC, while making multiple inserts as part of a transaction, this Exception was thrown:
java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:873)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:792)
As a consequence, the commit() statement was skipped and control went to a catch block where a rollback was attempted. With the connection being closed, this resulted in another Exception:
java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1170)
So the transaction was neither explicitly committed or rolledback by the client code.
The connection was returned to the pool (which I assume discarded it).
The program then continued by getting a new connection from the pool. It started a new transaction with setAutoCommit(false) and started reading data.
Surprisingly it read data written in the previous transaction (that I thought failed with the connection being closed by some external factor).
The program hasn't altered the transaction isolation level, so if I understand correctly, that should still be the default of READ_COMMITTED.
I'm trying to understand what has happened here. My understanding is that a local transaction occurs within a session and according to JDBC docs, a Connection equates to a session. Yet, by the only explanation I can come up with for the effects I've seen is that here a transaction persisted across two separate Connections and that the beginning of the transaction in the second Connection caused the commit of the previous half finished transaction in the previously used Connection.
Is this possible? Is this stuff documented anywhere?
Cheers,
Derek