Re: Fix set/get transaction isolation level test in ConnectionTest - Mailing list pgsql-jdbc

Your recommendation to leave the behavior as is and change the
regression test is fine with me.

thanks,
--Barry

Rene Pijlman wrote:
> 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 ^(TM) 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
>
>