Hi, All!

Interested reader can execute the following testcase at READ COMMITTED
isolation level with any combination of other TPB flags:

create table test (
test_val integer
);

Insert into test values (0);

commit;

Now, in multiple concurrent transactions do:

update test set test_val = test_val + 1;

This works correctly on all MGA and non-MGA databases we tried. Oracle,
PostgreSQL, MSSQL, etc.
Firebird fails.

And this is certainly a problem for serious applications.
Such applications often maintain aggregated views of large tables
(materialized views, if you like).
Triggers doing the maintenance follow the pattern I described and tend
to fail utterly often under load.

SQL was designed for this sort of thing. For the syntax of SQL UPDATE
statement, defenition of READ COMMITTED isolation level and allowed
anomalies there please refer to SQL standard (ISO/IEC 9075-2:2003).

GDML follows another paradigm of where reads and updates are explicitly
separated (you do not indicate that you open cursor FOR UPDATE). GDML
syntax looks like this:
====================================
FOR(REQUEST_HANDLE request TRANSACTION_HANDLE
gbl->gbl_transaction)
X IN RDB$EXCEPTIONS
WITH X.RDB$EXCEPTION_NAME EQ t

if (!DYN_REQUEST(drq_m_xcp)) DYN_REQUEST(drq_m_xcp) = request;

found = true;
MODIFY X
UCHAR verb;
while ((verb = *(*ptr)++) != isc_dyn_end)
switch (verb) {
case isc_dyn_xcp_msg:
GET_STRING(ptr, X.RDB$MESSAGE);
X.RDB$MESSAGE.NULL = FALSE;
break;

default:
DYN_unsupported_verb();
}
END_MODIFY;
END_FOR;
====================================
Separation of reads and modify operations and possibility of update
conflicts at READ COMMITTED isolation level appears obvious here.

Also note that when you open a cursor FOR UPDATE record is logically
updated _BEFORE_ it is returned by such cursor. This is true for Oracle
and other engines we tried so positioned updates can never fail with
UPDATE CONFLICT error. For Firebird this is true only if you use FOR
UPDATE WITH LOCK syntax. We'll probably need to fix it at some point in
the future.

For explanation how to use READ COMMITTED isolation level and why it is
the only isolation level applicable for complex concurrent OLTP
transactions I can only direct you to documentation and call for
experience.
Also I would also recommend to read something about the concept of
cursor stability at READ COMMITTED isolation level in particular.

To summaries, READ COMMITTED isolation level is somewhat broken in
Firebird. To cure it we need to do the following things:
1) implement cursor stability
2) cure update conflict errors in UPDATE and DELETE statements
3) cure update conflict errors for positioned updates
4) scrollable _stable_ cursors are trivial to implement and this doesn't
need much logic inside the engine or outside of it (SCROLLABLE_CURSORS
stuff appears to be unnecessary)

Nickolay