how a reference constraint can lead to a table lock in oracle

We experienced this problem while using the register module.
This module registers the meta information of a file in the FILES table, using an AUTONOMOUS TRANSACTION. The primary key, the ID of the file, is returned by register module.

---

Next consider we create an import procedure that stages the contents of this file in a target table. Every line in the file is stored in the target table as a new record. This target table contains the field FILE_ID, in which the id of the registered file is stored.
The field FILE_ID in the target table has a references to the field ID in the files table.

---

At the start of the import package we register the file. Next we insert the records in the target table. No commit was given. At the end we update FILES table with the number of records loaded.
If no index is created for the FILE_ID field, you receive an ora-60, deadlock, on the FILES table, which means that their is a table lock in place on this table.
When an index is create for the field FILE_ID in the target table, this issue is resolved.

---

conclusion

When you create a reference constraint, remember to also create an index for this constraint.
If you forget to create this index inserts or updates will lead to a table lock on the referencing table.