learntrade wrote:

>
> After initial setup and connection, the program starts a transaction
> and calls a stored procedure that performs an update to a record, and
> then selects the updated value from the record to return. The
> calling program additionally takes the returned value and performs an
> insert into a different table, still within the same transaction.
> Then, the transaction is committed.
>
> Multiple clients following this sequence, _should_ (I think) therefore
> all be "held" at the attempt to update the ("generator") record, until
> the other transaction (if any) is released.

Correct. But if the first transaction commits, then you'll get deadlock
(an update conflict, actually). Only if the first transaction rolls
back, then the second update will succeed. This is by design.

However, this is true for the rec_version mode only. With
no_rec_version, the waiting will be performed when *reading* the record
for update, so your scenario should succeed. But the problem is that
there's no explicit queue of record-level waiters, so if more than two
concurrent applications are in game, I'm afraid you cannot guarantee
their execution without conflicts.

BTW, I really doubt that IBPP uses no_rec_version by default for
read-committed transactions. But even working in the rec_version mode,
you may emulate the no_rec_version behaviour using the SELECT WITH LOCK
feature.


Dmitry