Oracle how child table behave when data from parent table is modified?

Question

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.

Answer 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.