CODEWITHSUNDEEP
oracle-databaselockingforeign-keysdeadlock
goldenhordes
goldenhordes

Reputation: 65

Oracle - Deleting row from child table locks parent table

We have deadlock issue in Oracle 11.2g. One potential reason why deadlock occurs is deleting from child table locks parent table. I searched from oracle documentations, and did't find any specification this kind of lock. Any explanation or reference of documentation would be greatly appreciated.

Here is the code.

CREATE TABLE table_parent (a NUMBER PRIMARY KEY);
CREATE TABLE table_child (b NUMBER, a NUMBER,PRIMARY KEY (b), CONSTRAINT fk_relation FOREIGN KEY (a) REFERENCES table_parent(a));

INSERT INTO table_parent VALUES (1);
INSERT INTO table_parent VALUES (2);
INSERT INTO table_child VALUES (1,1);
INSERT INTO table_child VALUES (2,1);
INSERT INTO table_child VALUES (3,1);
INSERT INTO table_child VALUES (4,1);

COMMIT;

Then delete 1 record from child table.

DELETE FROM table_child WHERE b=4;

When we look V$LOCK table before executing commit. There is two new lock that 'table_child' and 'table_parent' in type of 'TM'.

Here is the query to look V$LOCK table.

SELECT O.OWNER, O.OBJECT_ID, O.OBJECT_NAME, O.OBJECT_TYPE, L.TYPE
FROM DBA_OBJECTS O, V$LOCK L
WHERE O.OBJECT_ID = L.ID1;

The question is why 'table_parent' has been locked?

Upvotes: 4

Views: 9359

Answers (3)

Sushant Butta
Sushant Butta

Reputation: 530

An answer to this is explained with examples here http://www.oraclebin.com/2012/12/what-is-deadlock-in-oracle.html

Upvotes: 0

Toon Koppelaars
Toon Koppelaars

Reputation: 154

For maintaining constraints that 'span multiple rows', which is the case for a foreign key, there are moments when one (ie. the DBMS) needs to serialize transactions. The moments at which serialization is required depend one-on-one on the type of changes that a transaction performs on the involved tables. Theoretically (in a DBMS that offers snapshot isolation, which is what Oracle does), only if the type of change is such that it could potentially violate the multi-row constraint, would the DBMS need to automatically serialize transactions (for instance by acquiring various types of locks).

Now in the case of a foreign key, one needs to ask oneself: when can a foreign key be violated? There are four scenarios.

  • a parent row is deleted: will violate FK if child-rows still exist.
  • a parent row's key is updated: will violate FK if child-rows till 'point to' old value of key.
  • a child row is inserted: will violate FK if row points-to non-existent parent-row.
  • a child row's fk-column value is updated: will violate FK if new column value points to non-existent parent-row.

All other types of transactions on the (2) tables involved can never violate the FK. So in your case, a child row is deleted, no serialization should be necessary. However Oracle probably has some 'implementation specific' reason, which forces it to do acquire some kind of lock.

I've seen a different scenario where Oracle performs this kind of "unnecessary" locking too: you can find it here https://forums.oracle.com/forums/thread.jspa?messageID=10050753&#10050753

Toon

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52376

Add an index on the table_child(a) column -- you always index foreign key columns for just this reason.

Upvotes: 2

Related Questions