Re: [PATCHES] Fix JDBC test suite, set/get transaction isolation level test in ConnectionTest

The patch looks OK to me. (And yes a recent patch I did started using
set session characteristics, prior to that the code was explictly
setting the isolation level after every begin.)

thanks,
--Barry

Rene Pijlman wrote:
> 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 ^(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
>>
>
>
>
> ------------------------------------------------------------------------
>
> Index: org/postgresql/test/jdbc2/ConnectionTest.java
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/test/jdbc2/ConnectionTest.java,v
> retrieving revision 1.3
> diff -c -r1.3 ConnectionTest.java
> *** org/postgresql/test/jdbc2/ConnectionTest.java 2001/09/07 22:17:48 1.3
> --- org/postgresql/test/jdbc2/ConnectionTest.java 2001/09/08 13:59:54
> ***************
> *** 203,238 ****
> }
> }
>
> ! /**
> ! * Transaction Isolation Levels
> ! */
> ! public void testTransactionIsolation() {
> ! try {
> ! Connection con = JDBC2Tests.openDB();
> !
> ! con.setAutoCommit(false);
> !
> ! // These are the currently available ones
> ! con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
> ! assert(con.getTransactionIsolation()==Connection.TRANSACTION_SERIALIZABLE);
> !
> ! con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
> ! assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED);
> !
> ! // Now turn on AutoCommit. Transaction Isolation doesn't work outside of
> ! // a transaction, so they should return READ_COMMITTED at all times!
> ! con.setAutoCommit(true);
> ! con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
> ! assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED);
> !
> ! con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
> ! assert(con.getTransactionIsolation()==Connection.TRANSACTION_READ_COMMITTED);
> !
> ! JDBC2Tests.closeDB(con);
> ! } catch(SQLException ex) {
> ! assert(ex.getMessage(),false);
> ! }
> ! }
>
> /**
> * JDBC2 Type mappings
> --- 203,296 ----
> }
> }
>
> ! /**
> ! * Transaction Isolation Levels
> ! */
> ! public void testTransactionIsolation()
> ! {
> ! try
> ! {
> ! Connection con = JDBC2Tests.openDB();
> !
> ! // PostgreSQL defaults to READ COMMITTED
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! // Begin a transaction
> ! con.setAutoCommit(false);
> !
> ! // The isolation level should not have changed
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! // Now change the default for future transactions
> ! con.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
> !
> ! // Since the call to setTransactionIsolation() above was made
> ! // inside the transaction, the isolation level of the current
> ! // transaction did not change. It affects only future transactions.
> ! // This behaviour is recommended by the JDBC spec.
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! // Begin a new transaction
> ! con.commit();
> !
> ! // Now we should see the new isolation level
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> !
> ! // Repeat the steps above with the transition back to
> ! // READ COMMITTED.
> ! con.setTransactionIsolation(
> ! Connection.TRANSACTION_READ_COMMITTED );
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> ! con.commit();
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! // Now run some tests with autocommit enabled.
> ! con.setAutoCommit(true);
> !
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! con.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> !
> ! con.setTransactionIsolation(
> ! Connection.TRANSACTION_READ_COMMITTED );
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! // Test if a change of isolation level before beginning the
> ! // transaction affects the isolation level inside the transaction.
> ! con.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE );
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> ! con.setAutoCommit(false);
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> ! con.setAutoCommit(true);
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_SERIALIZABLE );
> ! con.setTransactionIsolation(
> ! Connection.TRANSACTION_READ_COMMITTED );
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> ! con.setAutoCommit(false);
> ! assertEquals( con.getTransactionIsolation(),
> ! Connection.TRANSACTION_READ_COMMITTED );
> !
> ! JDBC2Tests.closeDB(con);
> ! }
> ! catch ( SQLException ex )
> ! {
> ! fail( ex.getMessage() );
> ! }
> ! }
>
> /**
> * JDBC2 Type mappings
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
> patchConnectionTest.diff
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> quoted-printable
>
>
> ------------------------------------------------------------------------
> Part 1.3
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>