Subject Re: [firebird-support] The difference in queries
Author Helen Borrie
At 09:20 a.m. 11/03/2014, Łukasz Bączek wrote:

>This sentence:
>"SERIALIZABLE: This is the strictest isolation level, Which enforces transaction serialization. Date accessed in the context of a serializable transaction can not be accessed by any other transaction."

Here is the whole quote. The asterisks for emphasis were inserted by me:
Isolation Level: A transaction isolation level defines the interaction and visibility of work performed by simultaneously running transactions. There are four transaction isolation levels ****according to the SQL standard****:

READ COMMITTED: A transaction sees only data committed before the statement has been executed.

READ UNCOMMITTED: A transaction sees changes done by uncommitted transactions.

REPEATABLE READ: A transaction sees during its lifetime only data committed before the transaction has been started.

SERIALIZABLE: This is the strictest isolation level, which enforces transaction serialization. Data accessed in the context of a serializable transaction cannot be accessed by any other transaction.

Firebird supports only two of those ****SQL-standard**** isolation levels: READ COMMITTED and REPEATABLE READ (SNAPSHOT). It does not support READ UNCOMMITTED or SERIALIZABLE.

The most restrictive isolation level in Firebird is SNAPSHOT TABLE STABILITY, which dwells somewhere between the concepts of REPEATABLE READ and SERIALIZABLE. It denies writes to subsequent transactions and it cannot see table rows that currently have uncommitted writes. It comes close to the quality of SERIALIZABLE when the extremes of other transaction conditions (access mode; and table reservation) are applied; otherwise STS transactions don't block READ access. In practice, SNAPSHOT TABLE STABILITY should be avoided as it really has very few practicable use cases and, of course, it will make life for multiple users very frustrating.

By contrast, use of the pessimistic locking mechanism FOR UPDATE blocks all access to each row in turn as it is encountered by competing transactions. Once each row's pessimistic lock is secured, blocked read access remains for all subsequent transactions until the "offending" transaction is committed. IMHO, it was a pretty useless "enhancement" during the Firebird era, that was unnecessary and horribly difficult to use correctly. With Firebird you can get ample control over write conflicts using SNAPSHOT transactions, without jamming up the flow with pre-emptive blocking of any kind.

Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"