Dead Lock Detection.

A deadlock occurs when two or more session are waiting for data locked by each other, resulting in all the sessions being blocked. 

Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. 

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

In addition to the deadlock error reported to the session, a message is placed in the alert log.

ORA-00060: Deadlock detected. More info in file $OracleHome\udump\xxxx_ora_4200.trc.

The error message contains a reference to a trace file, whose contents indicate the SQL statements blocked in both the session that detected the deadlock and the other blocked sessions.

Sample Trace file:

Deadlock Graph

The first section is important; it shows the deadlock graph. Here are the various pieces of information on the graph. Deadlock graph tells you which sessions are involved, what types of locks are being sought after, etc.

Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)---------

Resource Name          process session holds waits  process session holds waits

TM-009f9121-00000000        85      54    SX   SSX       84       8    SX   SSX

TM-009f9121-00000000        84       8    SX   SSX       85      54    SX   SSX

session 54: DID 0001-0055-00002966 session 8: DID 0001-0054-0000541B 

session 8: DID 0001-0054-0000541B session 54: DID 0001-0055-00002966 

Row Information

The next critical section shows the information on the rows locked during the activities of the two sessions. From the tracefile you can see the object ID. Using that, you can get the object owner and the name from the DBA_OBJECTS view. The information in on rowID is also available here.

Deadlock due to Foreign Key

This is a really tricky one; but not impossible to identify. When a key value in parent table is updatd or a row is deleted, Oracle attempts to takes TM lock on the entire child table. If an index is present on the foreign key column, then Oracle locates the corresponding child rows and locks only those rows. The documentation in some versions may not very clear on this. There is a documentation bug (MOS Bug# 2546492). In the absense of the index, a whole table TM lock may cause a deadlock. Let's see the scenario when it happens.

Scenario

Here is the scenario when this deadlock occurs.

Step	Session1	Session2
1	Delete Chaild Row1
2		Delete Child Row2
3	Delete Parent Row1 (Waits on TM Enqueue)
4		Delete Parent Row2 (Waits on TM Enqueue) Deadlock!

Deadlock Graph

This is how the deadlock graph looks like when caused by unindexed foreign key. As you can see, the deadlock graph does not clearly say that the issue was to do with Foreign Key columns not being indexed.Instead, the clues here are:

• TM locks for both the sessions, instead of TX. Remember: TM are metadata related, as opposed to TX, which is a row related lock.
• The lock type of holders is Share Exclusive (SX) as opposed to Exclusive (X)
• Sessions do not show any row information

These three clues together show that this deadlock is due to FK contention rather than the conventional row locks. 

So, what do you do? Simple - create the indexes on those FKs and you will not see this again. As a general rule you should have indexes on FKs anyway; but there are exceptions, e.g. a table whose parent key is never updated or deleted infrequently (think a table with country codes, state codes or something pervasive like that). If you see a lot of deadlocks in those cases, perhaps you should create indexes on those tables anyway.

Deadlock due to Bitmap Index Contention

Bitmap Index is a special type of index that stores bitmaps of actual values and compare bitmaps to bitmaps, e.g. instead of comparing literals such as "A" = "A", Oracle converts the value to a bitmap and compares against the stored bitmap values. For instance “A” might be represented as "01011"; so the comparison will be “01011” = "01011". Index searches are way faster compared to literal comparison.

However, there is a price to pay for this performance. Unlike a regular b*tree index, when a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore udates 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. Here is the scenario when this condition arises.

Scenario                         

Step	Session1	Session2
1	Update Row 1 (Bitmap index piece is locked)
2		Update Row2 (Hangs for TX Row Lock)
3	Update Row2 (Hangs as bitmap index piece is locked by session2 and can't release until it commits) Deadlock!

Deadlock Graph

You can confirm this occurrence from readling the deadlock graph. 



The clues that show this type of deadlock:

• The lock type is TX (as shown in the Resource Name)
• 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.

The solution to this deadlock is really simple – just alter the application logic in such a way that the two updates will not happen in sequence without commits in between. If that’s not possible, then you have to re-evaluate the need for a bitmap index. Bitmap indexes are usually for datawarehouse only; not for OLTP. 

Deadlock due to Primary Key Overlap

This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. 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. Let's examine the scenario:

Scenario

Step	Session1	Session2
1	Insert PK Col value = 1 (Doesn't commit)
2		Insert PK Col value = 2 (Doesn't commit)
3	Insert PK Col = 2 (Hangs, until Session2 commits)
4		Insert PK Col = 1 (Hangs and Deadlock)

Deadlock Graph

The deadlock graph looks like the following. 


The key clues are:

• 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.

However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it's INSERT, remember?).

TKProf & SQL Trace