Re: revisiting transaction isolation

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