Subject RE: [Firebird-general] Snapshot Isolation in A Critique of ANSI SQL Isolation Levels
Author Jonathan Bond-Caron
On Fri Oct 15 06:43 PM, Ann W. Harrison wrote:
> from the 1995 paper by Berenson, Bernstein, Gray, Melton, O'Neil, &
> O'Neil. Does anyone know of a system actually implemented that works
> like this?
> My specific question is whether any system actually collects potential
> conflicts and analyzes them at commit time rather than recognizing the
> conflict on update and resolving it by requiring the second concurrent
> transaction trying to update or delete a row to wait for the outcome
> of the first transaction?

Short answer to your question: I don't think so. Sounds like a lot of
overhead/processing to keep track of.

For example, some databases don't bother with write conflicts:

PostgreSQL in SERIALIZABLE isolation level will not block/wait if
conflicting UPDATE statements happen.

It provides a "read snapshot in isolation" but allows 'conflicting writes'
to happen concurrently.
This is somewhat ok since all reads by concurrent transaction are isolated.

Oracle has a similar non-blocking approach, this is good for performance.

So both PostgreSQL and Oracle don't have the intuitive "SERIALIZABLE" but
something like "SERIALIZE READ"

On the other hand, true SERIALIZABLE would lock and wait:

I don't know the internals of SQL server but it sounds SQL Server 2000-2005
does collect potential conflicts to be analyzed on commit.

> A second, more general question is whether anyone knows of a database
> in commercial use that tries to order transactions based on their
> relative start time (as Reed does) rather than having each new
> transaction note the state of other transactions at that instant?

Good question, I think the commercial databases don't share enough their
internals :)

Using the "relative start time" sounds like a good approach.

Extra note: If you want to have to have a 'multi-master cluster of servers'
and allow a "SERIALIZE READ" or "SERIALIZABLE" transaction to happen, it
gets complicated!

So here, I imagine you need to keep track of all transactions (start time +
commit time) and "collect potential
conflicts and analyze them at commit time" but some sort of "commit
replication manager".