Henner Kollmann wrote:

> That's interesting. I firebird really killing the transaction or will it
> wait infinite until the client will kill the connection?

Firebird discovers deadlocks, picks a victim arbitrarily and sends an
error message. The victim should detect the error and rollback its
transaction, which will break the deadlock and allow processing to continue.

>
> Is it possible to wait only a few seconds and catch an error then?

Deadlock scans are performed when a lock request has waited for more
than 10 seconds.

> Is it possible to log which table is deadlocked?

No.

>

Here's how the situation actually happens in Firebird. When a
transaction starts, it takes out an exclusive lock on its transaction id.

>>Imagine the situation bellow in the exact order it is:
>>
>>User1:
>>starts transaction
>>update table1 set field2 = 'value' where id = 1;
>>(locked)

The record is NOT locked, but a new version of the record is written
that contains the transaction id of user1.

>>
>>User2:
>>starts transaction
>>update table1 set field2 = 'value' where id = 500;
>>(locked)

The record is NOT locked, but a new version of the record is written
that contains the transaction id of user2.

>>
>>User2:
>>update table1 set field2 = 'other value' where id = 1; (wait
>>the lock of User1)

When User2 tries to write a new version of the record, it (actually
Firebird on its behalf) encounters the record version created by User1.
Since User1 is active, Firebird tells User2 that it can not overwrite
an uncommitted change. Depending on the transaction type User2 either
gets an error immediately (no wait) or requests a lock on the
transaction id of User1.

>>
>>User1:
>>Update table1 set field2 = 'other value' where id = 500;

When User1 tries to write a new version of the record, it (actually
Firebird on its behalf) encounters the record version created by User2.
Since User21 is active, Firebird tells User1 that it can not overwrite
an uncommitted change. Depending on the transaction type User1 either
gets an error immediately (no wait) or requests a lock on the
transaction id of User2.

At this point, User1 is waiting for a shared lock on the transaction id
of User2 and User2 is waiting for a shared lock on the transaction id of
User1. As soon as one or the other has waited 10 seconds, Firebird
scans the lock table, looking for cycles in the wait-for graph. (That's
pigeon gook-speak for two transactions waiting for each other.)

>>-->DEADLOCK

Right. One of the two waiting transactions will get an error.

>>

Regards,


Ann