Re: (Revised) Question about deadlocks

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_at_tempus-consulting-group.com> 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
>
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l