Deadlock : The deadly Lock , is it really

Let's understand whats a deadlock

deadlock occurs when two or more users are waiting for data locked by each other. When this happens, these users are stuck (deadly embrased) 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: ORA-00060: deadlock detected while waiting for resource. Oracle will also write out a trace file with detailed information to the database's UDUMP directory.

Deadlocks are mostly caused by explicit locking because Oracle does not do lock escalation; nor use read locks. Multi-table deadlocks can be avoided by locking the tables in same order in all applications, thus preventing a deadlock condition.

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:

1) 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.

2) 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.

Welcome : Apps DBA Forum

Welcome : Apps DBA Forum
It is a resource for Oracle Database Administration professionals to disseminate information regarding DBA/APPS-DBA networking and training related events, Oracle Apps Consultants who have obtained Oracle Financials training and intend to further develop themselves through discussions and support through the internet....common platform to discuss Oracle and Oracle Applications issues from a DBA's perspective.