revisiting transaction isolation

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.

...

On a related topic I just took a look at the JDBC3 spec and it says:

> 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.

Should we follow the recommendation and have setTransactionIsolation()
defer until the next transaction when called mid-transaction? i.e. store
"current" vs "requested" isolation level; changing isolation level
mid-transaction only changes the requested level, and after
commit/rollback we issue an appropriate SET if current != expected.

-O