What is a deadlock and how does one fix deadlock errors?

orafaq

A deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embraced) and cannot continue processing.

Oracle automatically detects deadlocks and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. The session that is rolled back will observe Oracle error: : deadlock detected while waiting for resource. Oracle will also write out a trace file with detailed information to the database's UDUMP directory.

Multi-table deadlocks can be avoided by locking tables in same order (in all applications), thus preventing a deadlock condition. For example, session1 lock table: emp then dept; session2: emp then dept. If this is not possible, your application should check for ORA-60 errors and restart the rolled back transactions.

Here is an example of how to simulate a deadlock error:

Session 1 lock table EMP:

SQL> UPDATE emp SET sal=sal+100;
14 rows updated.

Session 2 lock table DEPT:

SQL> UPDATE dept SET loc = 'Japan';
4 rows updated.

Session 1 now update DEPT. The session will hang waiting for a lock (not a deadlock yet!):

SQL> UPDATE dept SET loc = 'Japan';

Session 2 now update EMP, causing the deadlock:

SQL> UPDATE emp SET sal=sal+100;

Oracle will detect the deadlock and roll back one of these statements:

SQL> UPDATE emp SET sal=sal+100;
UPDATE emp SET sal=sal+100
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Other more obscure deadlock situations one needs to be aware of:

• If you get ORA-60 errors on UPDATE and DELETE statements, where two processes wait for 'S' mode locks on each other's 'TX' enqueues, you are experiencing ITL shortage deadlocks". This cannot happen with INSERT statements, as Oracle doesn't wait on ITL (Interested Transaction List) slots for inserts, it will simply try to insert the row into the next available block.

To fix this, recreate the segment with higher INITTRANS and/or PCTFREE values. This will allow more space in the data blocks for Oracle to allocate more transaction entries (24 bytes at a time) when required.

Note: "ITL waits" can be monitored per segments by querying the sys.v_$segment_statistics view.

• High transaction activity on tables with bitmap indexes. Bitmap indexes are only appropriate in read only/ read mostly environments. You can try to use a very high INITTRANS value for the bitmap index, however, it would be best to disable the index before heavy transactional activity, and to rebuild it when done.

[@more@]