Subject RE: [Firebird-general] Snapshot Isolation in A Critique of ANSI SQL Isolation Levels
Author Jonathan Bond-Caron
On Mon Oct 18 12:53 PM, Ann W. Harrison wrote:
> On 10/16/2010 9:58 AM, Jonathan Bond-Caron wrote:
> > 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
> Hmmm... I think that PostgreSQL will send a fatal error to the second
> concurrent update if that transaction is in their "serializable"
> mode if the first updater commits, just as Firebird does. They have a
> "read committed" mode that is closer to "write committed" where the
> second updater waits and applies its update to the newly committed
> version. That's what InnoDB, MySQL's transactional storage engine,
> does. It gives the odd effect that SELECT and SELECT ... FOR UPDATE
> can give different results.

That would make sense, sounds like I have testing and homework to do

I always stick to FOR UPDATE locks instead of SERIALIZABLE isolation.

In terms of research, you might be interested in "Serializable Snapshot
Isolation (SSI)":