We observed that while issuing a delete statement, oracle is locking table(X) which is not impacted due to the specific delete statement.
Further analyzing the issue, we noticed that the locks are not there anymore on table(X) after dropping a foreign key on a child table which was referring to the table which is being deleted.
To explain in details, 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 if the id exists in that table.
No changes expected in other tables.
delete TABLE_A where id = 1234;
/*
Expted to update TABLE_C.TABLE_A_id to null due to ON DELETE SET NULL
*/
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
Anyone here to explain the reason why all 4 tables are getting locked ?