Oracle in World: Causes and Solutions of ORA-00054

Before reading this topic please have a look at the topic ora-00054: resource busy and acquire with nowait specified. At that post I have specified the reason and solution of ORA-00054 problem. Also have a look at DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT in order to have an idea about new feature exist in oracle 11g which will help to minimize ORA-00054 problem.

Explanation of ORA-00054 Error
Generally ORA-00054 is reported if a SQL statement would have blocked waiting for another user to complete some operation.

Issuing any of the following two type of SQL statements can cause ORA-54 problem.
i) SQL statement had a 'NOWAIT' clause so instead of blocking the statement returns this error.
or
ii) SQL statement was DDL operation and was blocked.

So for both DDL or SELECT .. FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.

Diagnosis of the problem
A) The V$LOCK view lists the locks currently held by the Oracle Database and outstanding
requests for a lock or latch.
Let's have a look at column of v$lock view.

SQL> desc v$lock;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 ADDR                                               RAW(4)
 KADDR                                              RAW(4)
 SID                                                NUMBER
 TYPE                                               VARCHAR2(2)
 ID1                                                NUMBER
 ID2                                                NUMBER
 LMODE                                              NUMBER
 REQUEST                                            NUMBER
 CTIME                                              NUMBER
 BLOCK                                              NUMBER

- Where TYPE indicates type of user or system lock.
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
The user type locks are:
i) TM - DML enqueue
ii) TX - Transaction enqueue
iii)UL - User supplied

The system type locks are:

1)   BL - Buffer hash table instance 
2)   NA..NZ - Library cache pin instance (A..Z = namespace)
3)   CF - Control file schema global enqueue 
4)   PF - Password File
5)   CI - Cross-instance function invocation instance 
6)   PI, PS - Parallel operation
7)   CU - Cursor bind 
8)   PR - Process startup
9)   DF - Data file instance 
10)  QA..QZ - Row cache instance (A..Z = cache)
11)  DL - Direct loader parallel index create 
12)  RT - Redo thread global enqueue
13)  DM - Mount/startup db primary/secondary instance 
14)  SC - System change number instance
15)  DR - Distributed recovery process 
16)  SM - SMON
17)  DX - Distributed transaction entry 
18)  SN - Sequence number instance
19)  FS - File set 
20)  SQ - Sequence number enqueue
21)  HW - Space management operations on a specific segment
22)  SS - Sort segment
23)  IN - Instance number 
24)  IR -Instance recovery serialization global enqueue 
25)  SV - Sequence number value
26)  IS - Instance state 
27)  TA - Generic enqueue
28)  IV - Library cache invalidation instance 
29)  TS - Temporary segment enqueue (ID2=0)
30)  JQ - Job queue 
31)  TS - New block allocation enqueue (ID2=1)
32)  KK - Thread kick 
33)  TT - Temporary table enqueue
34)  LA .. LP - Library cache lock instance lock (A..P = namespace)
35)  UN - User name
36)  MM - Mount definition global enqueue 
37)  US - Undo segment DDL
38)  MR - Media recovery 
39)  WL - Being-written redo log instance
40)  ST Space transaction enqueue

- LDOME is Lock mode in which the session holds the lock.
- REQUEST is Lock mode in which the process requests the lock.
- Both LMODE and REQUEST can have 0 to 6 value which means
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

B) To find out the blocked process issue,
SQL> select * from v$lock where request!=0;

C) If you see locks being taken out on a child table when doing deletes on the parent then indexing the foreign key column on the child table will stop happening error ORA-00054.

D) In order to find the list of locks in a database issue following query,

SQL> column lock_id1 format a10
SQL> set pagesize 120
SQL> column lock_type format a12
SQL> column mode_held format a10
SQL> column mode_requested format a10
SQL> column blocking_others format a20
SQL> column username format a10
SQL> SELECT     session_id
    ,  lock_type
    ,  mode_held
    ,  mode_requested
    ,  blocking_others
    ,  lock_id1
    FROM       dba_lock l
    WHERE      lock_type NOT IN ('Media Recovery', 'Redo Thread')
    /

SESSION_ID LOCK_TYPE    MODE_HELD  MODE_REQUE BLOCKING_OTHERS      LOCK_ID1
---------- ------------ ---------- ---------- -------------------- ----------
       159 XR           Null       None       Not Blocking         4
       159 Control File Row-S (SS) None       Not Blocking         0
       159 RS           Row-S (SS) None       Not Blocking         25
       153 AE           Share      None       Not Blocking         99
       158 Temp Segment Row-X (SX) None       Not Blocking         3
       161 PW           Row-X (SX) None       Not Blocking         1
       132 AE           Share      None       Not Blocking         99
       156 AE           Share      None       Not Blocking         99
       132 TO           Row-X (SX) None       Not Blocking         62986
       127 CU           Exclusive  None       Not Blocking         640372732
       127 AE           Share      None       Not Blocking         99
       127 FU           Exclusive  None       Not Blocking         0
       132 DML          Row-X (SX) None       Not Blocking         70833
       127 DML          Row-X (SX) None       Not Blocking         5750
       127 DML          Row-X (SX) None       Not Blocking         56048
       153 DML          Row-X (SX) None       Not Blocking         70833
       127 Transaction  Exclusive  None       Not Blocking         65550
       153 Transaction  Exclusive  None       Not Blocking         589833

18 rows selected.