Thomas Steinmaurer wrote:

> I hope this isn't that much off-topic here, but I do need some type of
> confirmation what SQL-compliant transaction isolation levels are
> supported in Firebird. ;-)
>
> Looking through different Firebird related documentations (Helen's book,
> IBP pdf set, ...), it seems that Firebird doesn't support the
> SQL-compliant transaction isolation level "REPEATABLE READ". Is this
> correct?
>

There's a fair amount of confusion about transaction levels on all
sides. The newest SQL standard I found says this (full text below):

An SQL-transaction has an isolation level that is READ UNCOMMITTED,
READ COMMITTED, REPEATABLE READ, or SERIALIZABLE.

OK, that's fine. Firebird supports READ COMMITTED, REPEATABLE READ (in
concurrency mode) and SERIALIZABLE (in consistency aka table locking
mode). The it says a bunch of stuff about when you can set the
transaction mode and what the default transaction mode is in various
cases. Then it says:

The execution of concurrent SQL-transactions at isolation level
SERIALIZABLE is guaranteed to be serializable.

Which seems self-evident. Further clarification follows:

A serializable execution is defined to be an execution of the
operations of concurrently executing SQL-transactions that
produces the same effect as some serial execution of those
same SQL-transactions. A serial execution is one in which
each SQL-transaction executes to completion before the next
SQL-transaction begins.

That's what you get with consistency mode, at some cost, to be sure.
Then the spec does something quite foolish and decides to define the
levels based on phenomena. The phenomena are specific to a lock-based
concurrency scheme. These people know better - they've written a good
paper on the subject that I have somewhere. However, they step in it here.

The isolation level specifies the kind of phenomena that can
occur during the execution of concurrent SQL-transactions.

The following phenomena are possible:

1) P1 (“Dirty read”): SQL-transaction T1 modifies a row.
SQL-transaction T2 then reads that row before T1 performs
a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a
row that was never committed and that may thus be considered
to have never existed.

OK so far. Firebird never does that.

2) P2 (“Non-repeatable read”): SQL-transaction T1 reads a row.
SQL-transaction T2 then modifies or deletes that row and
performs a COMMIT. If T1 then attempts to reread the row, it
may receive the modified value or discover that the row has
been deleted.

OK, that's what happens in READ COMMITTED

3) P3 (“Phantom”): SQL-transaction T1 reads the set of rows
N that satisfy some <search condition>. SQL-transaction T2
then executes SQL-statements that generate one or more rows
that satisfy the <search condition> used by SQL-transaction
T1. If SQL-transaction T1 then repeats the initial read with
the same <search condition>, it obtains a different collection
of rows.

OK, that happens in READ COMMITTED also. Here's where they make their
mistake:

The isolation levels are different with respect to phenomena P1,
P2, and P3. Table 8, “SQL-transaction isolation levels and the
three phenomena” specifies the phenomena that are possible and
not possible for a given isolation level.

Table 8 — SQL-transaction isolation levels and the three phenomena

Level P1 P2 P3

READ UNCOMMITTED Possible Possible Possible

READ COMMITTED Not Possible Possible Possible

REPEATABLE READ Not Possible Not Possible Possible

SERIALIZABLE Not Possible Not Possible Not Possible

NOTE 63 — The exclusion of these phenomena for SQL-transactions
executing at isolation level SERIALIZABLE is a consequence of the
requirement that such transactions be serializable.

Sigh. Our consistency transactions are better than repeatable read -
they don't allow phantoms as defined in the standard. They do allow
other non-serializable operations. So they're between repeatable read
and serializable.

If the authors of the standard were to take the obvious path and define
"Repeatable Read" to mean that reads are consistent within a
transaction, lock-based systems would suffer. The problem is called
"predicate locking" - locking something that doesn't exist - and is
usually done by locking access paths - index pages, end-of-table, etc.
Expensive and very prone to reporting deadlocks.

So, the word on our transaction levels per SQL standard is:

READ COMMITTED - yes
REPEATABLE READ - yes, without the phenomena described - better than yes.
SERIALIZABLE - yes, but at cost.

Regards,


Ann



Full text:

4.35.4 Isolation levels of SQL-transactions

An SQL-transaction has an isolation level that is READ UNCOMMITTED, READ
COMMITTED, REPEATABLE READ, or SERIALIZABLE. The isolation level of an
SQL-transaction defines the degree to which the operations on SQL-data
or schemas in that SQL-transaction are affected by the effects of and
can affect operations on SQL-data or schemas in concurrent
SQL-transactions. The isolation level of an SQL-transaction when any
cursor is held open from the previous SQL-transaction within an
SQL-session is the isolation level of the previous SQL-transaction by
default. If no cursor is held open, or this is the first SQL-transaction
within an SQL-session, then the isolation level is SERIALIZABLE by
default. The level can be explicitly set by the <set transaction
statement> before the start of an SQL-transaction or by the use of a
<start transaction statement> to start an SQL-transaction. If it is not
explicitly set, then the isolation level is implicitly set to the
default isolation level for the SQL-session before each SQL-transaction
begins. If no <set session characteristics statement> has set the
default isolation level for the SQL-session, then the default isolation
level for the SQL-session is SERIALIZABLE.

Execution of a <set transaction statement> is prohibited after the start
of an SQL-transaction and before its termination. Execution of a <set
transaction statement> before the start of an SQL-transaction sets the
access mode, isolation level, and condition area limit for the single
SQL-transaction that is started after the execution of that <set
transaction statement>. If multiple <set transaction statement>s are
executed before the start of an SQL-transaction, the last such statement
is the one whose settings are effective for that SQL-transaction; their
actions are not cumulative.

The execution of concurrent SQL-transactions at isolation level
SERIALIZABLE is guaranteed to be serializable.

A serializable execution is defined to be an execution of the operations
of concurrently executing SQL-transactions that produces the same effect
as some serial execution of those same SQL-transactions. A serial
execution is one in which each SQL-transaction executes to completion
before the next SQL-transaction begins.

The isolation level specifies the kind of phenomena that can occur
during the execution of concurrent SQL-transactions.

The following phenomena are possible:

1) P1 (“Dirty read”): SQL-transaction T1 modifies a row. SQL-transaction
T2 then reads that row before T1 performs a COMMIT. If T1 then performs
a ROLLBACK, T2 will have read a row that was never committed and that
may thus be considered to have never existed.

2) P2 (“Non-repeatable read”): SQL-transaction T1 reads a row.
SQL-transaction T2 then modifies or deletes that row and performs a
COMMIT. If T1 then attempts to reread the row, it may receive the
modified value or discover that the row has been deleted.

3) P3 (“Phantom”): SQL-transaction T1 reads the set of rows N that
satisfy some <search condition>. SQL-transaction T2 then executes
SQL-statements that generate one or more rows that satisfy the <search
condition> used by SQL-transaction T1. If SQL-transaction T1 then
repeats the initial read with the same <search condition>, it obtains a
different collection of rows.

The four isolation levels guarantee that each SQL-transaction will be
executed completely or not at all, and that no updates will be lost. The
isolation levels are different with respect to phenomena P1, P2, and P3.
Table 8, “SQL-transaction isolation levels and the three phenomena”
specifies the phenomena that are possible and not possible for a given
isolation level.

Table 8 — SQL-transaction isolation levels and the three phenomena

Level P3 P2 P1

READ UNCOMMITTED Possible Possible Possible

READ COMMITTED Possible Possible Not Possible

REPEATABLE READ Possible Not Possible Not Possible

SERIALIZABLE Not Possible Not Possible Not Possible

NOTE 63 — The exclusion of these phenomena for SQL-transactions
executing at isolation level SERIALIZABLE is a consequence of the
requirement that such transactions be serializable.

Changes made to SQL-data or schemas by an SQL-transaction in an
SQL-session may be perceived by that SQL-transaction in that same
SQL-session, and by other SQL-transactions, or by that same
SQL-transaction in other SQL-sessions, at isolation level READ
UNCOMMITTED, but cannot be perceived by other SQL-transactions at
isolation level READ COMMITTED, REPEATABLE READ, or SERIALIZABLE until
the former SQL-transaction terminates with a <commit statement>.

Regardless of the isolation level of the SQL-transaction, phenomena P1,
P2, and P3 shall not occur during the implied reading of schema
definitions performed on behalf of executing an SQL-statement, the
checking of integrity constraints, and the execution of referential
actions associated with referential constraints. The schema definitions
that are implicitly read are implementation-dependent. This does not
affect the explicit reading of rows from tables in the Information
Schema, which is done at the isolation level of the SQL-transaction.

NOTE 64 — The Information Schema is defined in ISO/IEC 9075-11.