oracle deadlock in one update statement?

Question

First my oracle version:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

I create table and insert two rows:

create table test_table
(
    objectId VARCHAR2(40) not null,
    dependId VARCHAR2(40) not null
);

insert into test_table values(1, 10000);
insert into test_table values(2, 20000);
commit;

Then open two sessions and execute the following commands in order.

Case1:

session1:

update test_table set dependId=100000 where objectid in (2);

session2:

update test_table set dependId=200000 where objectid in (1,2);

seesion1:

update test_table set dependId=100000 where objectid in (1);

and session2 showsORA-00060: deadlock detected while waiting for resource

Case2

session1:

update test_table set dependId=100000 where objectid in (1);

session2:

update test_table set dependId=200000 where objectid in (2,1);

seesion1:

update test_table set dependId=100000 where objectid in (2);

and no deadlock occur.

Please explain the reason. How the update ... where objectid in (1,2) hold the lock?

Are you saying that in **Case 2**, `update` issued in **session2** successfully updated the table? I don't think so. Perhaps you didn't get ORA-00060 message (i.e. the session kept waiting), but I'm quite certain that you didn't update 2 rows. — Littlefoot, Jun 27 '19 at 11:43
@Littlefoot - nope, Ticks is quite right: case 2 gives `2 rows updated.` in session #2 when session #1 commits. — APC, Jun 27 '19 at 12:02
Certainly, @APC - **when session #1 commits**. But until it does, session #2 waits. — Littlefoot, Jun 27 '19 at 12:09
Absolutely baffling. I have verified this behavior using separate SQL*Plus sessions on an Oracle 12c database. I inserted a few more rows into the table, and it seems that the ORA-00060 happens only when done in the order of Session 1 update high number, Session 2 update low, high number, Session 1 update low number. Otherwise session 2 simply hangs until session 1 commits. — Tad Harrison, Jun 27 '19 at 12:20
@Littlefoot In **Case2** session#2 waits, but will not deadlock, while in **Case1** session#2 will deadlock, I want to know why. — Ticks, Jun 27 '19 at 12:32

score 5 · Accepted Answer · answered Jun 27 '19 at 15:23

This comes down to the order the database tries to acquire locks on the rows.

In your example objectid = 1 is "first" in the table. You can verify this by sorting the data by rowid:

create table test_table
(
    objectId VARCHAR2(40) not null,
    dependId VARCHAR2(40) not null
);

insert into test_table values(1, 99);
insert into test_table values(2, 0);
commit;

select rowid, t.* from test_table t
order  by rowid;

ROWID                 OBJECTID    DEPENDID   
AAAT9kAAMAAAdMVAAA    1           99          
AAAT9kAAMAAAdMVAAB    2           0

If in session 1 you now run:

update test_table set dependId=100000 where objectid in (2);

You're updating the "second" row in the table. When session 2 runs:

update test_table set dependId=200000 where objectid in (2,1);

It reads the data block. Then tries to acquire locks on them in the order they're stored. So it looks at the first row (objectid = 1), asks "is the locked?" Finds the answer is no. And locks the row.

It then repeats this process for the second row. Which is locked by session 1. When querying v$lock, you should see two entries requesting 'TX' locks in lmode = 6. One for each session:

select sid from v$lock
where  type = 'TX'
and    lmode = 6;

SID   
    75 
    60

So at this stage both sessions have one row locked. And session 2 is waiting for session 1.

In session 1 you now run:

update test_table set dependId=100000 where objectid in (1);

BOOOOM! Deadlock!

OK, but how can we be sure that this is due to the order rows are stored?

Using attribute clustering (a 12c feature), we can change the order rows are stored in the blocks, so objectid = 2 is "first":

alter table test_table 
  add clustering 
  by linear order ( dependId );

alter table test_table move;

select rowid, t.* from test_table t
order  by rowid;

ROWID                 OBJECTID    DEPENDID   
AAAT9lAAMAAAdM7AAA    2           0           
AAAT9lAAMAAAdM7AAB    1           99

Repeat the test. In session 1:

update test_table set dependId=100000 where objectid in (2);

So this has locked the "first" row. In session 2:

update test_table set dependId=200000 where objectid in (2,1);

This tries to lock the "first" row. But can't because session 1 has it locked. So at this point only session 1 holds any locks.

Check v$lock to be sure:

select sid from v$lock
where  type = 'TX'
and    lmode = 6;

SID   
    60

And, sure enough, when you run the second update in session 1 it completes:

update test_table set dependId=100000 where objectid in (1);

NOTE

This doesn't mean that update is guaranteed to lock rows in the order they're stored in the table blocks. Adding or removing indexes could affect this behaviour. As could changes between Oracle Database versions.

The key point is that update has to lock rows in some order. It can't instantly acquire locks on all the rows it'll change.

So if you have two or more sessions with multiple updates, deadlock is possible. So you should start your transaction by locking all the rows you intend to change with select ... for update.

Excellent illustration Chris. I guess it will be much harder to reproduce a *deadlock* in case that both session use only [one `DML` statement](https://stackoverflow.com/q/70678272/4808122) in their transaction, but at least in the theory it should be possible, right? — Marmite Bomber, Jan 14 '22 at 12:57
@MarmiteBomber in theory yes, two updates starting at opposite ends of the table could block each other. One starts with the "first" row, the other "last". At some point they'll meet in the middle and one will block the other. The unblocked session will continue until it reaches a row locked by the blocked session. In reality the chances of this happening are miniscule. — Chris Saxon, Jan 14 '22 at 16:46

oracle deadlock in one update statement?

1 Answers1