Thread: 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
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 > >
Fix JDBC test suite, set/get transaction isolation level test in ConnectionTest
From
Rene Pijlman
Date:
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
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
Re: [PATCHES] Fix JDBC test suite, set/get transaction isolation level test in ConnectionTest
From
Barry Lind
Date:
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@postgresql.org) > > patchConnectionTest.diff > > Content-Type: > > text/plain > Content-Encoding: > > quoted-printable > > > ------------------------------------------------------------------------ > Part 1.3 > > Content-Type: > > text/plain > Content-Encoding: > > binary > >
Patch applied. Thanks. > 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