IBX TIBTransaction deadlocks no matter what I set it to
lazarus / 16-Databases

----------------------------------------------------------------------
RedOctober · 2024-03-13 22:41

Platform:  Windows Server 2016,  Lazarus 2.0.10 (Yes it's old, I will
be updating right after I solve this problem in this project) FPC
3.2.0, MWA IBX database components 2.3.4.9999, Firebird 3.07.3

I have several small automated application exes that do some updates
to some Firebird DB records, all running at the same time.  To ensure
I avoid deadlocks, I use the following on my transaction component
before using it (setting trnRGN.Active := True or using
trnRGN.StartTransaction):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
trnRGN.Params.Clear;  trnRGN.Params.Add('concurrency'); 
trnRGN.Params.Add('wait'); 
(I have made no changes to the default firebird.conf file)

Regardless of these settings, I *always* get a deadlock error when two
of the application .exes fight for a record.  I just want the
transactions to WAIT like I set them to.

I have fiddled with all the other settings listed in the IBX For
Lazarus User Guide that have to do with deadlocked transactions and I
always have the same result.

Does anyone have any knowledge they can share about how to force the
TIBTransaction component to OBEY the "concurrency" and "wait"
settings?  Or, do I need to tweak something else?

Thank you in advance for any help you can provide.

----------------------------------------------------------------------
rvk · 2024-03-14 06:49

Quote from: RedOctober on March 13, 2024, 11:41:07 pmRegardless of
these settings, I *always* get a deadlock error when two of the
application .exes fight for a record.  I just want the transactions to
WAIT like I set them to.
I wonder where you got the error exactly. Without seeing the flow of
updates and commits it's hard to say what's the problem.

Normally I would expect a isc_lock_timeout error. Not a isc_deadlock.

One think is for sure... the "wait" is not indefinite.
https://firebirdsql.org/rlsnotesh/new-lock-timeout.html

Quote... while WAIT performs a blocking wait which times out only when
the conflicting concurrent transaction ends by being committed or
rolled back.
So the error would appear when you do a commit. But if you already
changed a record, which is also changed by another transaction (and
committed), then your change can't continue.

https://ib-aid.com/en/how-to-track-deadlocks-in-firebird/

So we would need to know exactly what flow the read, updates and
commits have before seeing the problem.

----------------------------------------------------------------------
kwyan · 2024-03-15 06:46

Without the flow, we can only guess or just to tell you, in principle,
why it may have deadlock.

You need "concurrency" and "wait" in DB transaction but the purpose is
not to eliminate the deadlock. Say, if one transaction updates Record
A and then Record B; while another one updates Record B and then
Record A, it may have deadlock.

Both transaction can do the first step (concurrently):

Transaction #1 update and hold Record A
Transaction #2 update and hold Record B

Then when they go to next step, transaction #1 wait for Record B and
transaction #2 wait for Record A. This is deadlock.

To avoid deadlock, you need to ensure records to be updated are in
same sequence in any transactions.

----------------------------------------------------------------------
rvk · 2024-03-15 07:07

Quote from: kwyan on March 15, 2024, 07:46:35 amYou need "concurrency"
and "wait" in DB transaction but the purpose is not to eliminate the
deadlock. Say, if one transaction updates Record A and then Record B;
while another one updates Record B and then Record A, it may have
deadlock. Updating two different records isn't really the problem and
won't give you a deadlock.
Updating THE SAME record in two different places in different
transactions will (at the moment of commit).

Quote from: kwyan on March 15, 2024, 07:46:35 amBoth transaction can
do the first step (concurrently):
Transaction #1 update and hold Record A
Transaction #2 update and hold Record B
Then when they go to next step, transaction #1 wait for Record B and
transaction #2 wait for Record A. This is deadlock.This won't give a
deadlock. Two different records... no problem for both transactions...
they can just commit.

The following will give a deadlock when committing one of the
transactions.
(you can see the example at https://ib-aid.com/en/how-to-track-
deadlocks-in-firebird/)

Transaction #1 update and hold Record A
Transaction #2 update and hold Record A
commit #1
commit #2

You can look at the WITH LOCK clause to lock the record.
https://firebirdsql.org/refdocs/langrefupd21-notes-withlock.html

I myself, use a somewhat dirtier trick and that's to simulate a lock
with dummy edit/post/edit in the transaction when starting to edit...
so when you try that also at the beginning of another edit on the same
record, you'll get a 335544345 isc_lock_conflict and can act
accordingly. (I use read_committed/rec_version/nowait)

----------------------------------------------------------------------
RedOctober · 2024-03-15 13:38

Thanks to all for the excellent help.

Your assumptions are correct.  I was allowing all .exes to update the
exact same record at the exact same time.  I designed it this way on
purpose, thinking I would rely on the transactions waiting for the
previous transaction to finish, then proceed after that.  I mistakenly
thought that a "wait" transaction was capable of waiting for a
previous transaction for a specified number of seconds, before giving
up and declaring a deadlock. This I now see is not the case.  "wait"
seems to do nothing in the event that two transactions are attempting
to update the same record at the same time.  It just gives up
immediately declaring a deadlock.  I have since redesigned the process
to handle only one update at a time.  This means taking a performance
hit, but at least it will keep working.

----------------------------------------------------------------------
kwyan · 2024-03-15 15:56

Firebird may have more situation to raise error with the word
"deadlock".

However, we should have a correct concept of "deadlock". In a database
management system (DBMS), a deadlock occurs when two or more
transactions are waiting for each other to release resources, such as
locks on database objects, that they need to complete their
operations. As a result, none of the transactions can proceed, leading
to a situation where they are stuck or “deadlocked.”

Refer to the True deadlock section in the following webpage:

https://ib-aid.com/en/transactions-in-firebird-acid-isolation-levels-
deadlocks-and-update-conflicts-resolution/

Therefore, the case I quoted previously is a real deadlock case.

-------

In general, the following (just update same record) is a very typical
database update pattern and should not create true deadlock.
Transaction 2 should wait for Transaction 1 to commit (i.e. release
the record A) and then update A. DBMS can set the timeout value for
tranaction to wait for the record.

Transaction #1 update and hold Record A
Transaction #2 update and hold Record A
commit #1
commit #2