Database Reference
In-Depth Information
*** 2014-04-16 18:58:26.603
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Obviously, Oracle considers these application deadlocks a self-induced error on the part of the application
and, for the most part, Oracle is correct. Unlike in many other RDBMSs, deadlocks are so rare in Oracle they can be
considered almost nonexistent. 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 unindexed foreign keys.
(The number two cause is bitmap indexes on tables subject to concurrent updates). Oracle will place a full table lock
on a child table after modification of the parent table in three scenarios:
If you update the parent table's primary key (a very rare occurrence if you follow the rule of
•
relational databases stating that primary keys should be immutable), the child table will be
locked in the absence of an index on the foreign key.
•
If you delete a parent table row, the entire child table will be locked (in the absence of an index
on the foreign key) as well.
•
If you merge into the parent table, the entire child table will be locked (in the absence of an
index on the foreign key) as well.
Note this is only true in Oracle9i and 10g and is no longer true
in Oracle 11g Release 1 and above.
These full table locks are a short-term occurrence in Oracle9
i
and above, meaning they need to be taken for
the duration of the DML operation, not the entire transaction. Even so, they can and do cause large locking issues.
As a demonstration of the first point, if we have a pair of tables set up as follows, nothing untoward happens yet:
EODA@ORA12CR1> create table p ( x int primary key );
Table created.
EODA@ORA12CR1> create table c ( x references p );
Table created.
EODA@ORA12CR1> insert into p values ( 1 );
1 row created.
EODA@ORA12CR1> insert into p values ( 2 );
1 row created.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> insert into c values ( 2 );
1 row created.
Search WWH ::
Custom Search