Why ORA-00060: Deadlock Detected

Why ORA-00060: Deadlock Detected

 

A deadlock occurs when two or more session waiting for data locked by each other, resulting in all session being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are causes when transactions explicitly override the default locking of oracle in application design but not every time deadlock causes due to poor application code. Most of the time there are DBA related certain issues.

The most common cause of deadlocks is the normal row level locking, which is relatively easy to find. But that is not the only reason. ITL Shortage, Bitmap Index Locking, Lack of FK Index, Direct Path Load, Primary Key Overlap is also some of the potential causes.

When a deadlock occurs and one of the statements gets rolled back, Oracle records the incident in the alert log (‘D:\oracle\admin\SADHAN\bdump\ora_18301.trc’)and its corresponding tracefile. The tracefile shows more information about this error such as:

·         The session that cause it.

·         The effected session list.

·         The oracle SID and PID of the session.

·         The locked object row.

·         The full SQL statement causing this issue.

·         The system or Machine using this session

From the above information you can get some other useful information using another view like from Object_Id you can later get the object name and other details using DBA_OBJECTS view. Thus you must check primarily tracefile to come to the conclusion or exact cause of the deadlock.

Through this article I am trying to briefly describe the different situation to cause deadlock and the way to avoid them.

Deadlock due to row level locking:

Consider the situation where two different session performing update on particular table. Session1 updated row1 and still not committed and simultaneously Session2 updated row2 and does not committed now if Session1 try to update row2 which is already locked by Session1 (due to not committed), Session2 will wait; but this wait is forever, since Session1 is also waiting and can’t perform a commit or rollback until the wait is over, but the session1 wait will continue to exist until Session2 commits or rollback.

This is most common cause of deadlocks purely depend on application code and can only be solved by reducing the possibilities of occurrence of row locking.

Database lock are FIFO (First in First out) based, i.e. the session first waiting for the lock will get it first, before another session which started waiting for the same resource after the first session. There are two main type of row locking:

·        TM (Table Lock) – It is related to database structure change. Suppose if you executing a ‘SELECT’ query the table structure of that table remain same in that period. This lock protects the table structure being modified. Thus some one cannot add column during that query.

·        TX (Row Lock) – This is the row level locking; when a row is locked by a session this type of lock is acquired.

Deadlock due to ITL wait:

When a session locks a row, it put the information of lock in the header of the block called Interested Transaction List (ITL). Each ITL slot takes up 24 bytes.

Consider the situation when two session performing update operation on the same block. When Session1 update row1 (if there is no more free ITL and no room in block to create more) and simultaneously Session2 update row2 (if there is also no more free ITL and no room in block to create more). Now if you try to update row2 using session1 will hang because ITL slot is packed causing the session to wait on ITL. The Session2 lock can not be resolved until Session1 release the lock.

Select owner, object_name, value

from v$segment_statistics

where statistic_name = 'ITL waits' and value > 0;

Deadlock due to foreign key

When a key value of parent table is updated or a row is deleted, oracle attempts to create TM lock on the entire child table (in the absence of the index). If an index is present on foreign key column, then oracle locates the corresponding child rows and lock only those rows.

Consider the scenario when foreign key column not being indexed in the table and through one session1 delete child row1 and session2 delete child row2 and now if session1 try to delete parent row1 then there is TM lock of whole table and if now session2 try to delete parent row2 then there is a deadlock on whole table. The below three clues together shows that this deadlock is due to FK contention:

–         TM locks for both the session, instead of TX.

–         The lock type of holder is shared exclusive (SX).

–         And session does not show any row information.

To deal with the above situation you need create indexes on those foreign key.

Deadlock due to direct load:

When a table is loaded with Direct Path, the entire table is locked from further DML, until committed. This lock will cause deadlock when two sessions try to load into the same table and the whole table is already locked by other session. Following are the symptoms for this type of deadlock are:

–         Lock type is TM (as shown in the Resource Name)

–         Lock mode for both the holders and waiters is X (indicating a row lock)

–         No row information (since it is not really row-related)

Deadlock due to Bitmap index Contention:

When a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore update to any of the rows covered by that index piece hangs.

When two sessions update two different rows covered by the same index piece, they wait for each other. Following are some symptoms that show this type of deadlock:

–         The lock type is TX

–         The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.

–         The waiter waits with mode "S" instead of "X"

–         The row information is available but the object ID is not the ID of the table; but the bitmap index.

In the above situation you need to alter the application logic in such a way that the two update will not happen in sequence without commits in between. If it is not possible, then you have to re-evaluate the need for a bitmap index (usually it is only for datawarehouse not for OLTP). 

Deadlock due to Primary key Overlap:

This case of deadlock occurs during inserts only (not updates or deletes). When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Following are some symptoms or this type of deadlock:

–         The lock type is TX (row lock)

–         The holders are holding the lock in "X" (exclusive) mode

–         The waiters are waiting for locks in “S” mode, even when the locks type TX.

–         The subsequent parts of the tracefile don’t show any row information.