PostgreSQL JDBC driver Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) failures - Mailing list pgsql-hackers

From Dave Tenny
Subject PostgreSQL JDBC driver Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) failures
Date
Msg-id 3F303134.7050804@comcast.net
Whole thread Raw
List pgsql-hackers
I'm using 7.3.3 of the server, and pg73b1jdbc2.jar for the JDBC driver.

Various permutations of calls to the setTransactionIsolation all fail to
yield a driver state that will tell me that it is set up for
serializable transactions.

Help!?

I googled, I grepped.  I find mention of problems with respect to JDBC
tests failing and patched, but see no reports of this as a bug,
or any fixes for it.

Here's the output from the attached program:

javac -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation.java &&
java -classpath .:/usr/share/pgsql/pg73b1jdbc3.jar TxnIsolation
supportsTransactions(): true
Default transaction isolation is 2
supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED):
false
supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED):
true
supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ):
false
supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): true
Current  transaction isolation is 2
Transaction isolation after setting to TRANSACTION_SERIALIZABLE is 2
^^^^^^^^^^^^^^^^^^^^ HAVE A PROBLEM ^^^^^^^^^^^^^^^^^^^^
Current auto commit is true
After settting  auto commit to false, it is false
Transaction isolation after setAutoCommit() is 2
Current connection readonly status is false
Connection readonly after setting it to be readonly is true

---------------------------------------------------------------

Moving the setTransactionIsolation call to before the setAutoCommit
call also results in failure.

To run the program, you'll need to change the database and userid
parameters in the DriverManager.getConnection call to something that
works for you.
import java.sql.* ;
import java.io.* ;
import java.util.* ;

/**
 * Test JDBC transaction/connection stuff.  In particular isolation levels.
 */


class TxnIsolation
{
  public static void main(String[] args) {
    try {
      // ----------------  MySql driver -----------------
      // -classpath .:/usr/java/mysql-connector-java-2.0.14/mysql-connector-java-2.0.14-bin.jar
      // RH9: -classpath .:/home/dave/mysql-connector-java-3.0.8-stable/mysql-connector-java-3.0.8-stable-bin.jar
      //Class.forName("com.mysql.jdbc.Driver") ;
      //Connection conn = DriverManager.getConnection("jdbc:mysql:///test", "dave", "") ;
      //String innodb = " TYPE=INNODB" ;

      // --------------- postgres 7.3.2 distribution JDBC2 driver linked as /usr/java/postgresql-jdbc.jar
      // -classpath .:/usr/java/postgresql-jdbc.jar
      Class.forName("org.postgresql.Driver") ;
      Connection conn = DriverManager.getConnection("jdbc:postgresql:test", "dave", "") ;
      String innodb = "" ;


      // Here we go.

      // Metadata
      DatabaseMetaData md = conn.getMetaData() ;
      System.out.println("supportsTransactions(): " + md.supportsTransactions()) ;
      System.out.println("Default transaction isolation is " + md.getDefaultTransactionIsolation()) ;
      System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED): " +
             md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)) ;
      System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED): " +
             md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)) ;
      System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ): " +
             md.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)) ;
      System.out.println("supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE): " +
             md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) ;

      // transactionIsolation
      // Theoretically important to call BEFORE setAutoCommit
      System.out.println("Current  transaction isolation is " + conn.getTransactionIsolation()) ;
      conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) ;
      System.out.println("Transaction isolation after setting to TRANSACTION_SERIALIZABLE is "
             + conn.getTransactionIsolation()) ;
      if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
    System.out.println("^^^^^^^^^^^^^^^^^^^^ HAVE A PROBLEM ^^^^^^^^^^^^^^^^^^^^") ;

      // autoCommit
      System.out.println("Current auto commit is " + conn.getAutoCommit()) ;
      conn.setAutoCommit(false) ;
      System.out.println("After settting  auto commit to false, it is " + conn.getAutoCommit()) ;
      System.out.println("Transaction isolation after setAutoCommit() is "
             + conn.getTransactionIsolation()) ;


      // readOnly
      System.out.println("Current connection readonly status is " + conn.isReadOnly()) ;
      conn.setReadOnly(true) ;
      System.out.println("Connection readonly after setting it to be readonly is " + conn.isReadOnly()) ;

    }
    catch (Exception e) {
      e.printStackTrace() ;
    }
  }                    // main()
}

pgsql-hackers by date:

Previous
From: Andreas Pflug
Date:
Subject: Re: Release changes
Next
From: Neil Conway
Date:
Subject: Re: Release changes