Subject Re: [firebird-support] Issue: deadlock with Firebird and Delphi. ref/eDN7002040583
Author Ann W. Harrison
Dennis wrote:
> Hello guys, I still have no response from anyone, is it so strange? Doesn't
> anybody have faced similar problem?

Not exactly, at least as far as I understand what you're doing. The
confusion is largely my fault, for historic reasons.

When the API that Firebird implements was being designed, two competing
groups were required to use it for two very different implementations of
a relational database. The requirement was that a program run against
one database or the other simply by changing the link that defined the
shared library it used. So, not only the calls but also the system
tables and the error messages had to be common. Since the two databases
were radically different internally, we agreed to a common subset of
major messages, with variations in minor messages to explain in more
detail what actually went wrong.

The family of deadlock messages were one of those cases. Our various
update conflicts are not actually deadlocks in the sense that two or
more transactions are interlocked. However, the correct error handling
is the same for update conflicts and real deadlocks - abandon the
operation and (usually) rollback and retry. So, when you say "I had
a deadlock" it's hard to know exactly what happened.

The problem gets worse as you add transaction options - an autocommit
transaction is quite different from a concurrency transaction and the
wait and no-wait transactions are also different.

> I have made this test: there is small application that on its start up
> updates the same record (with the same keys).

What transaction modes?

> When I call this application
> instantly several times, for instance 15 times in less than 1/10second, some
> of these applications, about 4, occur the deadlock conflict error on non
> wait transaction.

OK, that error means that the transaction is trying to update a record
that was updated by a concurrent transaction.
> At first sight, this is logical because some of them didn't commit yet, so
> there are deadlock really,

Probably not. A true deadlock happens when Transaction A tries to
update a record previously updated by Transaction B and Transaction B
tries to update a record previously updated by Transaction A. Both
wait forever. But your transactions (as least one of them) are
no wait.

> the problem is that using the follow retry loop
> these launches cannot post even if the record became unlocked, even if all
> other application / launches are terminated!

I've cut off the order of operations because I don't know what
a Repeat, Try, or Post is.

If you get a deadlock error on an update, you won't be able to
update that record in that transaction every. Commit or rollback
and retry. In theory, a read-committed transaction could re-read
the record after the other transaction commits, but that's not the
way it works.