CODEWITHSUNDEEP
oracle-databaseplsqldeadlock
Lukas H
Lukas H

Reputation: 123

PLSQL Oracle 12c deadlock, why is an SSX Table lock aquired for independent deletes?

I have the following two queries resulting in a deadlock. But do not know why Oracle tries to make an SSX Table lock in this scenario.

All test samples tried to replicate the problem, do only row locking.

                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TM-000386AF-00000000-00000000-00000000        101     298    SX   SSX  65474      27     646    SX   SSX  21533
TM-000386AF-00000000-00000000-00000000         27     646    SX   SSX  21533     101     298    SX   SSX  65474


Query:
DELETE FROM VERSANDPALETTE V WHERE V.ID IN (SELECT COLUMN_VALUE FROM TABLE(:B1 ))


----- Information for the OTHER waiting sessions -----
Session 646:
DELETE FROM VERSANDPALETTE WHERE ID IN (SELECT * FROM TABLE(:B1 )) AND ID NOT IN (SELECT * FROM TABLE(:B2 ))

I would expect, that the independent rowsets are deleted and no table lock is created.

Does someone has a hint on why that could happen?

EDIT 2: (Simplified Version of the question, 2 min to replicate)

Thanks for your help!

I used this code to test it further:

-- setup
create table p ( x int primary key );
create table c ( x references p );
insert into p select rownum from dual connect by level <= 10;
insert into c select * from p;
commit;

-- 2 session test

-- session 1
update c set x = 2 where x = 1;

-- session 2
update c set x = 4 where x = 3;
delete from p where x = 3;

-- session 1
delete from p where x = 1;

-- deadlock is happening now

-- rollback both sessions

This leads to a deadlock as expected, because there is no index on the child table fk. (as you pointed out to me)

What confuses me is, when only one session is used that there are only locked_mode 3 v$locked_object open. There should be a locked_mode 5 row somewhere.

-- 1 session test
update c set x = 2 where x = 1;
update c set x = 4 where x = 3;
delete from p where x = 3;
delete from p where x = 1;

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine,
a.locked_mode
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id 
and
a.object_id = c.object_id;

-- no locked_mode 5 entries...
-- rollback the session

Adding an index resolves the problem:

CREATE INDEX c_index ON c(x);

-- 2 session test
-- session 1
update c set x = 2 where x = 1;

-- session 2
update c set x = 4 where x = 3;
delete from p where x = 3;

-- session 1
delete from p where x = 1;

-- deadlock is not happening :)

So I guess there is some lock escalation going on? Because the single session test does not aquire the same table lock.

Upvotes: 1

Views: 785

Answers (1)

eaolson
eaolson

Reputation: 15094

As krokodilko says, do you have any dependent tables with a foreign key and the on delete cascade option? The SSX lock is to prevent inserts to the child table for a parent table that has had a row deleted.

See: https://asktom.oracle.com/pls/apex/asktom.search?tag=deadlock-on-two-delete-statements

Upvotes: 2

Related Questions