IF they update the same rows in different orders, the junior (later start time)
process will deadlock and be rolled back.
It is usually too slow and expensive to make that bullet proof by preemptively
ordering all updates by rowid or using single row by row (aka as slow by slow)
processing.
A few things can help a lot. You’ve already noticed keeping tables and columns
in the same order in all updates that are multiple statements.
Designing the updates so access tends to be in the same row order for batches
is very often enough.
It can make a big difference to ship the code for a transaction (or have it in
the database to begin with, referenced by name) along with the logic (if any)
to commit or roll back on various errors and outcomes, so that no individual
transaction is stranded over the network for even the line turnaround time.
Whether or not you use stored procedures in PL/SQL, an old paper of mine
“Minimizing the Concurrency Footprint of Transactions” may be useful reading
and may help understand what is going on in Oracle.
IF, in your current system you rarely (maybe not yet at all) see deadlocks in
simple concurrent batches of mostly different rows, then it *MIGHT* be useful
to trap deadlocks and count them, resorting to row by row after some reasonable
number of attempts rather than losing the transaction. Of course if the
transaction is really large, that is a recipe for a long wait, so mileage will
vary about whether losing the transaction requested is preferable to the
exception bailout loss of performance to salvage a transaction row by row or at
a smaller monolith commit size (possibly shrinking per failure). Not all
transactions can be simply replayed though, and you’ll have to evaluate that.
Good luck.
From: Matt McPeak [mailto:mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx] ;
Sent: Thursday, July 08, 2021 4:12 PM
To: 'Oracle List'; Mark W. Farnham
Subject: Re: Question about locks and deadlocking
Yes, I did, thank you. Everything should read "TABLE_A". Any idea whether
they'll deadlock and, if not, how Oracle prevents it given that each update
cannot process all its rows instantaneously?
On Thursday, July 8, 2021, 04:00:54 PM EDT, Mark W. Farnham <mwf@xxxxxxxx>
wrote:
I think you slapped a TABLE_B in there when you meant the same TABLE_A.
Otherwise the answer is the trivial there is no conflict.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Matt McPeak
Sent: Thursday, July 08, 2021 3:18 PM
To: Oracle List
Subject: Question about locks and deadlocking
Oracle experts,
I (think) I understand how application design problems can lead to deadlocks.
E.g.,
USER1: UPDATE TABLE_A SET COLUMN_A = 'X' WHERE ID = 100;
USER2: UPDATE TABLE_A SET COLUMN_A = 'X' WHERE ID = 101;
USER2: UPDATE TABLE_A SET COLUMN_A = 'X' WHERE ID = 100; (waits for user1)
USER1: UPDATE TABLE_A SET COLUMN_A = 'X' WHERE ID = 101; (deadlocks)
To avoid this, user1 and user2 should have done their updates in the same order.
Something I never thought about before is this scenario:
USER1: UPDATE TABLE_A SET COLUMN_A = 'X' WHERE ID BETWEEN 100 AND 200;
USER2: UPDATE TABLE_B SET COLUMN_A = 'X' WHERE ID BETWEEN 100 AND 200 AND
EXISTS (... something else that throws off execution plan maybe);
I thought I understood that, as each transaction processes rows, it adds itself
to the ITL of every block it touches and flags each row as locked by that ITL
entry. If that is the case, what guarantees that both transactions touch rows
in the same order. That is, what guarantees that these two updates do not
deadlock?
I don't think I've ever encountered in 25 years two bulk update statements
deadlocking by themselves. But what exactly has been saving me?
Thanks in advance!
Matt
