Subject Re: [IB-Architect] (Jim, Ann) Firebird tx isolation levels
Author Ann W. Harrison
At 08:05 AM 9/22/2002 -0400, David Jencks wrote:

>TRANSACTION_SERIALIZABLE <--> isc_tpb_consistency + isc_tpb_wait

Yes. That's table level locking and pretty brutal, but
it is serializable, which concurrency is not. Ideally,
it's used with a "reserving" clause so all locks are taken
up front in a non-deadlocking manner.

>TRANSACTION_REPEATABLE_READ <--> isc_tpb_concurrency + isc_tpb_wait

Yes. This is actually somewhat more consistent that some
repeatable read modes, but that's the best description.

>TRANSACTION_READ_COMMITTED <--> isc_tpb_read_committed +
>isc_tpb_rec_version + isc_tpb_wait


>TRANSACTION_READ_UNCOMMITTED <--> isc_tpb_read_committed +
>isc_tpb_rec_version + isc_tpb_wait

I would say "UNSUPPORTED", but if you must, I guess that's
close enough.

> Nickolay Samofatov thinks (see bug [ 575397 ] SERIALIZABLE is not
>isc_tpb_consistency) that this is wrong and that our driver should emulate
>interclient and map TRANSACTION_SERIALIZABLE and
>TRANSACTION_REPEATABLE_READ to isc_tpb_concurrency + isc_tpb_wait.

That a pragmatic attitude, but concurrency is not serializable in
a couple of cases. If, for example, you run a series of inserts
like this:

insert into t1 (f1) select count (*) from t1

You'd expect that f1 should be monotonically increasing. Unfortunately

The other case is this:

update t1 (f1) select f1 from t2 where <condition>
update t2 (f1) select f1 from t1 where <condition>

When executed by two concurrent transactions, these two statements
reverse the values of f1 between the two tables.

> Therefore I think this
>mapping should be based on what is most useful for the most users (which,
>of course, I think the current mapping supplies).

I tend to agree - having two modes that translate to the same
transaction parameters doesn't seem smart. On the other hand,
consistency is so very brute force...

>1. isc_tpb_concurrency does have some update anomalies that mean that it
>really shouldn't be labelled serializable


>2. isc_tpb_consistency is stronger isolation than isc_tpb_concurrency


>3. it is most useful for users of the driver to have more choices, so
>mapping 2 jdbc levels to one firebird level will reduce the usefulness of
>the driver.


>(we already map read uncommitted onto read committed!)

I'd map it to a random number generator.


We have answers.