Hi Matt,
Of course, you can get deadlock with 2 simple updates. That's pretty easy
to reproduce:
Full example:
https://gist.github.com/xtender/d06f6dd9e7213d84a9e5521fd92f98e3
1. Create a simple small table with an index:
create or replace function xt_sleep(res in int, sleep_sec in int) return int
is
begin
dbms_lock.sleep(sleep_sec);
return res;
end;
/
create table xt_test as select level n from dual connect by level<=10 order
by n desc;
create index ix_test on xt_test(n);
As you can see, "N" in the table is in "desc" order, so full table scan and
index full scan will rows in different order.
Then just execute these 2 updates in different sessions:
update/*+ full(t) */ xt_test t set n=n*10 where xt_sleep(n, 5)=n;
and
update/*+ index(t) */ xt_test t set n=n*100 where xt_sleep(n, 5)=n;
I've added the function xt_sleep here to make them slower, so it's easier
to get a deadlock event if you start second session a few seconds later .
On Thu, Jul 8, 2021 at 11:13 PM Matt McPeak <
mcpeakm@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
(small but important typo corrected from original question)
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_A 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
