Fix JDBC test suite, set/get transaction isolation level test in ConnectionTest

Attached is a patch that fixes
ConnectionTest.testTransactionIsolation() in the JDBC driver's
test suite. This reduces the number of failures of the test
suite from 7 to 6. The patch fixes the test case itself, rather
than the driver.

In addition to the change described in my posting below, I fixed
the part of the test with autocommit enabled. The author of the
test assumed that setting the transaction isolation level would
have no effect, but in fact it does. Perhaps the test case
worked with pre-7.1 behaviour, when the JDBC driver set the
isolation level in every transaction, instead of using "set
session characteristics". Anyway, now it works with a backend
built from current CVS and the behaviour is JDBC compliant.

I also extended the test case by changing the isolation level
before beginning a transaction and verifying it inside the
transaction.

Regards,
René Pijlman

On Fri, 7 Sep 2001 17:56:59 +0200, I wrote on pgsql-jdbc:
>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl