due to foreign key relations, oracle locks a table which will not be affected by the delete statement (on delete set null / on delete cascade)

Question

We observed that while issuing a delete statement, oracle is locking tables which are not impacted due to the specific delete statement.

Consider 4 tables as below.

create table TABLE_A (id number(14,0) not null primary key, code varchar2(30));
create table TABLE_B (id number(14,0) not null primary key, code varchar2(30));

create table TABLE_C (id number(14,0) not null primary key, code varchar2(30), TABLE_A_id number(14,0));
create table TABLE_D (id number(14,0) not null primary key, code varchar2(30), TABLE_C_id number(14,0), TABLE_B_id number(14,0));

Create foreign key references.

alter table TABLE_C  add constraint TABLE_C_fk1  foreign key (TABLE_A_id) references TABLE_A on delete set null;

alter table TABLE_D  add constraint TABLE_D_fk1  foreign key (TABLE_C_id) references TABLE_C on delete cascade;
alter table TABLE_D  add constraint TABLE_D_fk2  foreign key (TABLE_B_id) references TABLE_B on delete cascade;

While deleting a record from TABLE_A as below, it will also update the TABLE_A_id column to null in TABLE_C table if the id exists in that table. No changes expected in other tables.

delete TABLE_A where id = 1234;

This is working as expected in terms of the data modified. But when we look at the tables which are getting locked during this transaction we observed that all 4 tables are being locked.

LOCKED_MODE                              OBJECT_NAME                                                                                                                     
---------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
Row-X (SX)                               TABLE_A                                                                                                                 
Row-X (SX)                               TABLE_C                                                                                                                   
Row-X (SX)                               TABLE_B                                                                                                                 
Row-X (SX)                               TABLE_D

used following query to fetch the details of locked objects during the transaction.

SELECT Decode(lo.locked_mode, 0, 'None', 
                              1, 'Null (NULL)',
                              2, 'Row-S (SS)',
                              3, 'Row-X (SX)',
                              4, 'Share (S)',
                              5, 'S/Row-X (SSX)',
                              6, 'Exclusive (X)',
                              lo.locked_mode) locked_mode,
        o.object_name
FROM   v$locked_object lo
       JOIN dba_objects o ON o.object_id = lo.object_id
       JOIN v$session s ON lo.session_id = s.sid

Why oracle locks all 4 tables in this scenario ? Can someone explain the reason ?

due to foreign key relations, oracle locks a table which will not be affected by the delete statement (on delete set null / on delete cascade)

0 Answers0