Re: (Revised) Question about deadlocks

The answer is "luck" (plus a little care in the design and coding of the application).
You're absolutely correct to think that the two updates could deadlock; and you do see questions on the forums occasionally from people asking why exactly this problem has occured.
Usually it's because two updates that look fairly similar have been driven through different indexes, or one has gone by index the other by tablescan.

Regards
Jonathan Lewis

On Thu, 8 Jul 2021 at 21:13, Matt McPeak < mcpeakm_at_tempus-consulting-group.com> wrote:

>
> 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?
>
>
>
>

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