--- In firebird-support@yahoogroups.com, "Stan" <shizius@...> wrote:

>
> I apologize, the deadlock was not happening on commit, it was
> happening on the query.
>
> BUT, often, Firebid cannod detect the deadlocks and all my clients
> are just blocking on the query. I waited about 10 minutes for them to
> unblock, they did not. Why does this happen?

Stan.

It will only 'block' when in 'WAIT' mode.
When in 'NOWAIT' mode, you will receive an exception immediately.

What you must understand is the lock conflict mechanism that Firebird
uses prevents dirty updates etc.

In a snapshot / concurrency transaction, if a record version was not
committed at the time your transaction started, and you try to update
that record, you will receive a lock conflict, something like:

deadlock
-update conflicts with concurrent update

In NOWAIT, you receive this exception immediately. In WAIT, you only
receive this exception if the other transaction commits (your
transaction crosses its fingers that the conflicting transaction rolls
back).

Until the transaction that has modified the record commits (or
rollback), no-one will be able to modify it no matter how many 10
minute delays you put in your retry logic.

Further, because your transaction starts before the transaction that
wrote the latest record version for the updated record, your
transaction will never be able to modify it, ever. You will need to
commit that transaction and put the retry in a new transaction.

Now if you never seem to get around this lock conflict, it probably
tells you that you have not properly handled your transaction starts
and commits.

I am not sure how these rules vary for read_committed as I never use that.

Adam