|
Lacking indexes on columns involved in a foreign key will also cause deadlocks in Oracle. This problem is common. "Obviously, Oracle considers deadlocks a self-induced error on part of the application and, for the most part, they are correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be considered almost non-existent. Typically, you must come up with artificial conditions to get one. "The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys. There are two cases where Oracle will place a full table lock on a child table after modification of the parent table:
a) If I update the parent table’s primary key (a very rare occurrence if you follow the rules of relational databases that primary keys should be immutable), the child table will be locked in the absence of an index. b) If I delete a parent table row, the entire child table will be locked (in the absence of an index) as well... "So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:
a) You do not delete from the parent table. b) You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools! c) You do not join from the parent to the child (like DEPT to EMP).
If you satisfy all three above, feel free to skip the index – it is not needed. If you do any of the above, be aware of the consequences. This is the one very rare time when Oracle tends to ‘over-lock’ data." -Tom Kyte, Expert One-on-One Oracle First Edition, 2005. |
If you really don’t want one there should be a hint/pragma to turn it off.
It’s just such a stupid reason for a full table scan.