Subject Re: [Firebird-Architect] "Write Committed" transaction mode
Author Jim Starkey
On 11/29/2010 7:06 PM, Helen Borrie wrote:
> At 11:03 AM 30/11/2010, Jim Starkey wrote:
>> In both Falcon and NimbusDB, a select for update (either implicit or
>> explicit) in "write committed" mode cause a dummy update similar to an
>> Interbase/Firebird deleted record stub, but with a distinctive record
>> number indicating "record lock."
> So, it's a pessimistic-optimistic lock..? ;-)
> It seems to me more sensible/obvious than the hairy (IMHO) mechanism behind FOR UPDATE WITH LOCK as implemented in Fb 1.5. Could you describe with just a little more detail (with reference to the current situation with Snapshot and both of the resolution options of ReadCommitted ) the lock conflicts that could occur and the options for resolving them.

The purpose of the hack is to handle the case where an application is
using a record to hand out unique ids. These leads to massive
congestion in MVCC systems where every transaction blocked on the record
must fail and restart. This is the problem that drove me to create

The algorithm is simple, though the details might not be. When a record
is fetched "for update" in write committed mode:

1. Look at the head record version. If the transaction that created
it is committed, do a dummy update and return.
2. If the transaction that created the head version is active, wait
for it to complete. Then go back to step #1

A select for update will always block until it sees a committed record
that it can update. It is possible, even likely, that a select for
update will see a different version that an earlier ordinary select. A
subsequent ordinary select will, of course, see the version selected for

Like all update conflicts, it can deadlock. It isn't pretty, it isn't
elegant, but it gets the job done.

A generator / sequence is a much better solution, but that really isn't
the issue, which is really bonehead benchmarks.

Jim Starkey
Founder, NimbusDB, Inc.
978 526-1376

[Non-text portions of this message have been removed]