Subject Re: [Firebird-Architect] "Write Committed" transaction mode
Author Dmitry Yemanov
Ann W. Harrison wrote:

> Postgres, InnoDB (the MySQL transactional engine), and Oracle
> implement another isolation mode between "Snapshot" and
> "Read Committed." Normal Select statement works just as
> they do in Firebird's Snapshot mode.

What's exactly meant here regarding selects? Do they really work like in
snapshot, i.e. not seeing committed data of other transactions? I doubt
so. And it's surely not "read committed" in PGSQL. I rather believe you
were talking about the cursor stability, i.e. the statement being run
(or cursor being fetched from) doesn't see concurrent modifications
committed after statement start. But the next statement in the same
transaction will see those committed data (under the same rules).

> Update, delete, and select for update wait if there's a
> newer version of the record than the transaction can see,
> then succeed when the transaction that created the
> newer version commits.

So it differs from our read-committed mode in the fact that it
overwrites the data after a concurrent commit, not throw an update
conflict as we do. Did I get it right? This has been mentioned a few
times and I do agree it can be useful in various usage scenarios.

But implementation looks tricky at the first glance. We cannot proceed
at the VIO layer, because the modified record may affect the predicate
our update/delete/lock is based on, i.e. the modified record should no
longer be visible for us and thus be skipped, not overwritten. It means
that we have to unwind the whole RSB stack, re-fetch all the streams,
re-evaluate all the booleans and if everything still looks well re-try
the modification. This is surely possible for some statements, but
hardly problematic for others (DISTINCT, GROUP BY, etc). So I'm not
surprised Oracle may re-start the whole statement in this case. Is it
really the cost we should pay? Do I miss anything?