Subject Re: [Firebird-Java] interleaved transactions and deadlocks
Author Roman Rokytskyy

> I wondered if somebody could explain me why FB behaves differently
> when two deadlocked transactions are executed in parallel either from
> two connections or from a single one ?

I have forwarded your email to engine developers, my ideas are below.

> FB has the ability to run multiple transactions on a single
> connection. Now when two transactions that deadlock each others are
> executed on two different connections, the DB immediately detects the
> deadlock and an exception is thown.
> Now when you create the very same deadlock between two transactions
> running on the same connection (you can do that using the XAResource
> interface) the DB has to wait until after the period of time specified
> in the DeadlockTimeout DB config parameter elapsed.
> Can somebody explain me the difference ? Heikki Linnakangas seems to
> believe this is due to some deadlock that has nothing to do with
> transaction isolation but that makes no sense to me as I have no
> experience with DBMS engines.

In general FB throws two different types of exceptions - one for deadlock
and one for lock timeout. And in different places of the engine different
types of locks are requested. I have a list with places of what lock type is
requested where (and corresponding error codes), but I have to confirm that
from _my_ POV these are not consistent.

I guess that we have to create different test cases and then start the
discussion in the FB-Architect list. On the other hand, I really-really
doubt that things will be changed, since there's so many applications that
might (and do) rely on this feature, that changing it would break them.

But anyway, the test cases (or just an explanation) would be useful, at
least for documentation purposes.


PS. BTW, do you know that starting FB 2.0 the lock timeout can be controlled
via TPB. That means that in XA-mode we can reduce the differences to one
case - "immediate" deadlock notification.