Subject Re: [firebird-support] Re: Deadlock, Insert question
Author Helen Borrie
At 10:31 AM 5/09/2005 +0000, you wrote:
>So in short - Sinse I ALWAYS use a generator via a trigger the primary
>I should never recieve a deadlock (or an exception).
>So I have to look elsewhere to find source of the deadlock.
>Also - I have to double check, that no others are inserting a record
>with a fixed value as primary key.

I don't think it has anything to do with keys and that it is not a deadlock
of the kind described by Ivan. Unfortuntely the engine reports nearly all
locking conflicts as deadlocks, even when they are something other than the
"true" deadlock that Ivan describes. [You will get the exact type of
conflict by looking at the gdscode that is passed along with that sqlcode].

What appears to be happening here is that you have one (older) uncommitted
transaction (tx1) that has a set from this table selected in ReadCommitted
isolation. The newer transaction (tx2) comes along and tries to insert a
record that would, if permitted, affect the scope of that set. This is the
one situation where the Fb/IB engine doesn't fit its boast that readers
don't block writers (except when you make it happen intentionally, by using
a CONSISTENCY setup). As long as your uncommitted insert could make a
difference to the composition of the other transaction's set, it either has
to wait (if the locking policy is WAIT) or capitulate (if the locking
policy is NO WAIT) and receive a locking exception.

Change the isolation of the transaction containing the SELECT statement to
CONCURRENCY and the problem won't arise, since the set is not at risk of
being altered by tx2's inserts.