Re: revisiting transaction isolation

The code bellow throw an exception and I can see any transaction in progress.

Dario.

        String url   = getJdbcUrl();
        try {
            isCon = false;
            con = DriverManager.getConnection( url, usr, pwd );
            if ( con == null ) return false;
            isCon = true;
            con.setAutoCommit( autoCommit );
            int tiso  = con.getTransactionIsolation();
            try {
                    dmd = con.getMetaData();
                    int defTxIsolation  = -1;
                    if ( dmd != null && dmd.supportsTransactionIsolationLevel( tranIsolation ) ) {
                        defTxIsolation = dmd.getDefaultTransactionIsolation();
                    }
                    if ( tranIsolation >= 0 && tranIsolation != defTxIsolation ) {
                        tranIsolation = defTxIsolation;
                    }
                }
                if ( tranIsolation >= 0 ) {
                    con.setTransactionIsolation( tranIsolation );
                    tiso = con.getTransactionIsolation();
                }
            } catch ( SQLException se ) {
                se.printStackTrace();
                lastErrMsg = "\n ERROR: TransactionIsolation No Soportado = " + Tools.InfoException( se );
            }


Oliver Jowett wrote:

Kris Jurka wrote:

On Sat, 17 Jul 2004, Oliver Jowett wrote:

Currently, this type of code will fail:

 conn.setAutoCommit(false);
 if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
   conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

The problem is that getTransactionIsolation() issues a query and thus starts a new transaction, and then setTransactionIsolation() complains you can't change isolation level mid-transaction.

I'm not sure this is reasonable behaviour. One option is to make getTransactionIsolation (and what other methods too?) not cause a BEGIN to occur if there is no transaction in progress and autocommit is off.

I see no reason for getTransactionIsolation or any driver call to start a transaction, these are only SELECTs and won't be rolled back anyway.

It's ok for getTransactionIsolation(), but what about, say, metadata queries -- we do want transaction isolation to apply there even if it's just a SELECT, right?

A quick skim of AbstractJdbc2Connection turns up these methods as candidates for ignoring autocommit:

 - getTransactionIsolation()
 - getPGType (both versions) on a cache miss

It seems reasonable to suppress BEGINs for both of those cases. I can put together a patch to do that. I'll leave the metadata queries alone for the moment.

The return value of the method getTransactionIsolation should reflect
the change in isolation level when it actually occurs. [...]

This seems confusing and error prone.

Ok -- I'll leave that alone too.

-O