Thread: BUG #1251: setTransactionIsolation does not seem to work

The following bug has been logged online:

Bug reference:      1251
Logged by:          Fernando Bellas

Email address:      fbellas@udc.es

PostgreSQL version: 7.4.3

Operating system:   Red Hat 7.1

Description:        setTransactionIsolation does not seem to work

Details:

Hi,

First of all, the version I am using of PostgreSQL is 7.4.5, and *not* 7.4.3
(I have set 7.4.3 in the form, since it does not allow to choose 7.4.5).

I have tested several JDBC drivers (pg74.213.jdbc3.jar, pg74.215.jdbc3.jar,
pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar), and
connection.setTransactionIsolation does not change the transaction isolation
level (maybe the problem is with the database and not with the JDBC driver).

The following code prints "2" (TRANSACTION_READ_COMMITED) as the value of
the transaction isolation levels.

connection.setAutoCommit(false);
int oldTransactionIsolation =
  connection.getTransactionIsolation();
System.out.println("oldTransactionIsolation = " +
     oldTransactionIsolation);
connection.setTransactionIsolation(
    Connection.TRANSACTION_SERIALIZABLE);
System.out.println("newTransactionIsolation = " +
    connection.getTransactionIsolation());

Thanks in advance!
Fernando.

> Bug reference:      1251
> PostgreSQL version: 7.4.3
> Description:        setTransactionIsolation does not seem to work
>
> First of all, the version I am using of PostgreSQL is 7.4.5, and *not* 7.4.3
> (I have set 7.4.3 in the form, since it does not allow to choose 7.4.5).
>
> I have tested several JDBC drivers (pg74.213.jdbc3.jar, pg74.215.jdbc3.jar,
> pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar), and
> connection.setTransactionIsolation does not change the transaction isolation
> level (maybe the problem is with the database and not with the JDBC driver).
>
> connection.setAutoCommit(false);
> connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

By calling setAutoCommit(false) you are beginning a transaction and you
may not change the isolation level once a transaction has begun.  You
should move the isolation level calls before the auto commit setting.

The 8.0 driver does not suffer this problem because it does not begin a
transaction immediately upon the auto commit call, but waits for a real
query.  Then when setTransactionIsolation is called it will either work
correctly or issue an error stating that you are in a transaction.  The
8.0 driver can be used against earlier servers if you'd like.

Kris Jurka