At 06:26 PM 1/3/2002 +0100, Alessandro Ortuso wrote:

>Program A inserts a number of records in the table T1 and
>a trigger on the AfterInsert update a value on the table T2
>refering to the T1 record Program B does about the same
>operations
>
>These programs run at the same times in a transaction so the program B
>could insert records in T1 (and updating the refering record in T2) that
>are already modified from program A.
>This is a tipical deadlock situation as A cannot insert in T1 as is
>waiting for B to finish and the same B for A.
>
>How do you solve this problem?

I'm not entirely sure what you're seeing because, in theory,
InterBase/Firebird should detect the conflict between A and
B and raise an error for one or the other. The conflict has
nothing to do with the records that are being stored, just
those being updated.

There are, in fact, two cases. For the sake of discussion,
we have a table, T1 which can contain records a..z.

In the first case, A modifies T1-a then B tries to modify T1-a.
In that case, B waits for A to complete (commit or rollback)
then either completes (if A rolls back) or gets an error (if
A commits). If B has specified "no wait" in its transaction
declaration, then it will get an error as soon as it tries to
update T1-a.

In the second case, A modifies T1-a. B modifies T1-b. No
problem. Then B attempts to modify T1-a and goes into the
wait described above. Then A attempts to modify T1-b, causing
a classic deadlock in which neither A nor B will ever complete.
In that case, IB/FB chooses a victim and returns an error to
that victim.

In either case, the transaction receiving the error should
rollback and retry.

Where is the question?

Ann




Regards,

Ann
www.ibphoenix.com
We have answers.