Fix set/get transaction isolation level test in ConnectionTest

The ConnectionTest test case in our own jdbc2 test suite fails
to set and get the transaction isolation level correctly. After
looking at the implementation I've come to the conclusion that
the test case itself is flawed, but I wanted to check my
conclusion with this list.

What the test case does is:

con.setAutoCommit(false);

con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE)
;
assertEquals(Connection.TRANSACTION_SERIALIZABLE,
con.getTransactionIsolation());

And this assertion fails because con.getTransactionIsolation()
returns TRANSACTION_READ_COMMITTED.

The cause of this problem is that first a new transaction is
started (because of the setAutoCommit(false)) and then the
isolation level for this connection is changed. Internally
(since I tested against a 7.1 backend which supports SET
SESSION) the driver generates:

set session characteristics as transaction isolation level
serializable;

And this changes only the default isolation level for future
transactions on this session, not the isolation level of the
current transaction. Therefore, getTransactionIsolation() in the
same transaction returns the still current isolation level READ
COMMITTED.

Reading through JDBC documentation from Sun I found the best
explanation in the JDBC 3.0 Spec, final draft 3 (relevant
section quoted below). This says "It is recommended that drivers
implement the setTransactionIsolation method to change the
isolation level starting with the next transaction", and this is
in fact what our driver does.

It also says "Committing the current transaction to make the
effect immediate is also a valid implementation", but I see no
reason to change the current behaviour to this alternative
implementation.

And it says "The return value of the method
getTransactionIsolation should reflect the change in isolation
level when it actually occurs", and again, this is in fact what
our driver does.

Note that applications can avoid this complication simply by
setting the transaction isolation level before starting a
transaction (before calling setAutoCommit(false)), as
recommended by JDBC.

So I'm inclined to change the test case to allow (in fact,
require) the current behaviour. Any comments?

-+-+-
Quote from the "JDBC ™ 3.0 Specification, Proposed Final Draft
3"
http://java.sun.com/products/jdbc/download.html

10.2.1 Using the setTransactionIsolation Method
The default transaction level for a Connection object is
determined by the driver
supplying the connection. Typically, it is the default
transaction level supported by
the underlying data source.
The Connection method setTransactionIsolation is provided to
allow JDBC
clients to change the transaction isolation level for a given
Connection object. The
new isolation level remains in effect for the remainder of the
session or until the next
invocation of the setTransactionIsolation method.
The result of invoking the method setTransactionIsolation in the
middle of a
transaction is implementation-defined.
The return value of the method getTransactionIsolation should
reflect the
change in isolation level when it actually occurs. It is
recommended that drivers
implement the setTransactionIsolation method to change the
isolation level
starting with the next transaction. Committing the current
transaction to make the
effect immediate is also a valid implementation.
It is possible for a given JDBC driver to not support all four
transaction isolation
levels (not counting TRANSACTION_NONE). If a driver does not
support the isolation
level specified in an invocation of setTransactionIsolation, it
is allowed to
substitute a higher, more restrictive transaction isolation
level. If a driver is unable to
substitute a higher transaction level, it throws an
SQLException. The
DatabaseMetaData method supportsTransactionIsolationLevel may be
used to determine whether or not the driver supports a given
level.
-+-+-

Regards,
René Pijlman