0

Scenario: We have table A (Parent Table) referring to table B (Child Table) and also we have Foreign Key Index for every Foreign Key.

Operation: Now when any user is deleting a row from table A then table B is getting locked even if there are no referring record in child table. Because of this other user cannot do anything on table A anymore since the table is locked.

Understanding: I suppose when there are some child record exists in table B then the selected row should only be locked if the parent record is involved in some transaction and other users can still work on other rows of table A.

Question: How does it work when we have foreign key and foreign key index are created but there are no child record exists. The whole table is still locked? If yes how to get rid of it then?

Note: I am using Oracle 12c.

Sujit Kumar Swain
  • 21
  • 4
  • 1
    Why do you think the whole B table gets locked? You cannot delete a row from A without first deleting B unless you have a FK with cascading delete configured. – OldProgrammer Sep 18 '20 at 14:56
  • Could you put the structure of tables and also sample data to simulate the behaviour ? – Sujitmohanty30 Sep 18 '20 at 15:05
  • I think the whole table B is locked because when second user is trying to delete another record from table A then its waiting for first user to commit his changes. If this would just lock the record then it shouldn’t be the case right? – Sujit Kumar Swain Sep 19 '20 at 21:42
  • oracle doesn't lock child table in exclusive mode in case of covering foreign key index. Of course, child table will be locked in shared mode, that means you can't drop it, b it you still can insert and delete rows from child table, except those rows that related to the locked rows in parent table.Again why do you think that the whole table gets locked? You can easily check it in v$lock and event from v$session – Sayan Malakshinov Sep 19 '20 at 22:51

1 Answers1

1

You should have indexes on all foreign keys, otherwise you'll get TM table locks:

  1. https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:292016138754
  2. http://www.oaktable.net/content/tm-locking-checking-missing-indexes-foreign-key-constraints

Now when any user is deleting a row from table A then table B is getting locked even if there are no referring record in child table.

If you have index on foreign key in child table, you get only TX row locks in child table. If you foerign key is not indexes, you get TM table lock on whole child table.

Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • And what if there is fk and fk index but no child record? Will it still lock the child table? – Sujit Kumar Swain Sep 19 '20 at 21:46
  • No, oracle doesn't lock child table in exclusive mode in case of covering foreign key index. You still can insert and delete rows from child table, except rows related to the locked rows in parent table – Sayan Malakshinov Sep 19 '20 at 22:46