Oracle deadlock SX/SSX caused by no index on foreign key.

Example to show the dead lock caused by lack of index on foreign key of child table.

Session 1:

create table p ( x int primary key );
create table c ( x references p );

insert into p select rownum from all_users where rownum <= 2;
insert into c select * from p;
commit;
delete from c where x = 1;

 

Session 2:

prompt delete from c where x = 2;
prompt delete from p where x = 2; -- session 2 will be hang.

Session 1:

delete from p where x = 1; -- session1 will be hang, deadlock will be detected after a while.

 

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-00012c11-00000000 47 138 SX SSX 45 136 SX SSX
TM-00012c11-00000000 45 136 SX SSX 47 138 SX SSX

session 138: DID 0001-002F-00000176 session 136: DID 0001-002D-00000098
session 136: DID 0001-002D-00000098 session 138: DID 0001-002F-00000176

Rows waited on:
Session 138: no row
Session 136: no row

----- Information for the OTHER waiting sessions -----
Session 136:
sid: 136 ser: 207 audsid: 384548 user: 100/TEST flags: 0x45
pid: 45 O/S info: user: SYSTEM, term: PRICNESSD, ospid: 7044
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: PRICNESSD\Administrator, term: PRICNESSD, ospid: 6880:8016
machine: WORKGROUP\PRICNESSD program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
delete from p where x=2

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=dyg3c78z0ft6g) -----
delete from p where x=1
===================================================

The typical identifer for this is two SQL waiting on delete statement on one table, both transaction hold SX lock waiting on SSX lock.

Create on index on the foreign key column will resolve this problem.

create index c_idx on c(x);