Johannes Pretorius wrote:

>
> At 08:33 PM 22/06/2006, you wrote:
>> Complications include constraints (e.g. unique, primary key) with
>> the NO_REC_VERSIONS transaction option, triggers that update other
>> tables.
>
>
> My I ask what the complications are with NO_REC_VERSIONS (as this
> is the case with these transactions) ?
>

When your transaction uses the NO_REC_VERSIONS option, it will not
read an older version of the record even if the current version is
either not committed (for a read-committed transaction) or was not
committed when the transaction started (for a concurrency transaction).
Instead, it gives an update conflict error. Somebody sometime thought
that was clever. Beats me.

Anyway, the result is that you see deadlocks when there is no real
conflict. In your case, the insert recognizes that there is a potential
conflict on a unique or primary key constraint, and since it is a
no-wait, no-rec-version transaction gives you an deadlock error.
Without those options, it would wait until the conflicting transaction
completes, succeed if it failed, and fail if it commits, leaving you
ready to retry the select with certainty that it will succeed.


Regards,


Ann