Ann,

> No, Firebird doesn't lock records - record level concurrency is
> handled through versioning, so you can't do that.
> >
> > If it worked like Oracle's "SELECT ... FOR UPDATE", waiting for the
> > lock to succeed, then it would be perfect.
>
> The problem with Oracle's SELECT ... FOR UPDATE is that it breaks
> repeatable read - in fact, it allows you to update a record you
> can't see - which is pretty much the same as a dirty write.

I am not convinced yet ;-)

I just set up two instances of a test scenario (actually I did it with
IBExpert).

I set the TX parameters to

isc_tpb_read_committed + isc_tpb_rec_version + isc_tpb_wait

I then fired a

SELECT ID, COUNTER FROM COUNTERS WHERE ID = 1 FOR UPDATE WITH LOCK

from one instance. Assuming there is a record with ID 1 in this table
I will get the expected answer, e.g. COUNTER = 10

If I now execute the same command from another client, the client
will wait (isn't 10 seconds the default value for this?).

If during this time the first client updates the counter value to 11
and ends the TX with a commit, then the second client awakes and
returns a counter value of 11.

Isn't this what I wanted to have? Or do I miss something?

> Sorry, I was thinking of a three-layered application. The mechanisms
> that come to mind involve shared memory, so they're not applicable.

Though I already thought about something like that myself.
But I would prefer a "database solution" so that I can stick to 2-tier
solution.

> I think the best solution is to use very short transactions to get
> the "sequence" value and expect that most of them will have to rollback
> and retry several times.

Yes, I will probably do it that way if the "solution" mentioned above
does not work.

--Heiko