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:
#yiv6915077503 #yiv6915077503 -- _filtered {} _filtered {}#yiv6915077503
#yiv6915077503 p.yiv6915077503MsoNormal, #yiv6915077503
li.yiv6915077503MsoNormal, #yiv6915077503 div.yiv6915077503MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:New;}#yiv6915077503
a:link, #yiv6915077503 span.yiv6915077503MsoHyperlink
{color:blue;text-decoration:underline;}#yiv6915077503 a:visited, #yiv6915077503
span.yiv6915077503MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv6915077503
span.yiv6915077503EmailStyle17 {color:#1F497D;}#yiv6915077503
.yiv6915077503MsoChpDefault {font-size:10.0pt;} _filtered {}#yiv6915077503
div.yiv6915077503WordSection1 {}#yiv6915077503
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
