Re: Fix JDBC test suite, set/get transaction isolation level - Mailing list pgsql-jdbc

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

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

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026