What is an Oracle deadlock?

Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

Deadlocks in Oracle result in this error:

ORA-00060: deadlock detected while waiting for resource

If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

Inside Oracle deadlock machinations

The LMD process also handles deadlock detection Global Enqueue Service (GES) requests. Remote resource requests are requests originating from another instance.

Deadlock Detection

Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes. The following script can be used to identify deadlocks in the database. The query depends upon objects that are created by the script $ORACLE_HOME/rdbms/admin/dbmslock.sql. Log on as SYS or with SYSDBA authority and run this script in all databases. You may have to run the deadlock monitoring script below numerous times before you identify the transaction that is causing the problem.

The Enqueue Deadlock Per Sec Oracle metric is the number of times per second that a process detected a potential deadlock when exchanging two buffers and raised an internal, restartable error.

Avoiding Deadlock Conditions

A deadlock can occur whenever multiple users are in a waiting pattern for data locked by each other. Deadlocks prevent some transactions from continuing to work. In the event of deadlock, Oracle writes the message and error in the form of an ORA-60 error to the Oracle alert.log file. The following diagram illustrates the perfect storm condition that causes a deadlock or deadly embrace to occur within Oracle .

Deadlock problems have a similar root cause as that found with basic locking issues with Oracle which is the result of poor database application design. To resolve deadlock conditions with Oracle, the DBA needs to work together with the developer and software engineering team to modify or rewrite the database application code so that such deadlocks do not reoccur.

Lock Contention Issues and Solutions

After the database administrator has exhausted possibilities to visit the design of the database application with the development team, the next step is to perform further analysis to solve lock contention issues.

Oftentimes, the lock issue is the result of a zombie batch process or hung database session which has placed an exclusive lock on a specific row or table, thereby blocking access to the data from other users.

The simple solution to this type of problem is to identify the particular user and session causing the blocking condition and then to contact the user so that the session can be killed using the alter system kill session 'sid,serial#' immediate command from within SQL*Plus. In the previous section, numerous locking scenarios and potential solutions were covered.

Resolving Oracle deadlocks

There are several remedies for resolving aborted tasks from deadlocks:

Tune the application - Single-threading related updates and other application changes can often remove deadlocks. Re-scheduling batch update jobs to low-update times an also help.
Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve deadlocks.
Use smaller blocks with less data - Since the deadlock contention is at the block-level, consider moving these tables and indexes to a super-small blocksize (create a db2k_cache_size), and using a high PCTFREE to space-out the data over MORE blocks.

ORA-00060 and INITRANS Shortage

There can also be a ORA-00060 "deadlock detected" where the table and index INITRANS is set too low. The "Interested Transaction List" and deadlocks caused by an ITL-shortage as described in MOSC note 62354.1. The eBook "Oracle Space Management Handbook" also has notes on the internals of ITL.
You can also see ITL waits in a STATSPACK or AWR report, in the segments section we see:

Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.

Also, Arup Nanda has scripts for detecting ITL waits:

Select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   ' ' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p
where
   s.sid = l.sid and
   s.username <> ' ' and
   s.paddr = p.addr and
   l.type <> 'TM' and
   (l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   object_name object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   sys.dba_objects o
where
   s.sid = l.sid and
   o.object_id = l.id1 and
   l.type = 'TM' and
   s.username <> ' ' and
   s.paddr = p.addr
union
select
   s.sid SID,
   s.serial# Serial#,
   l.type type,
   '(Rollback='||rtrim(r.name)||')' object_name,
   lmode held,
   request request
from
   v$lock l,
   v$session s,
   v$process p,
   v$rollname r
where
   s.sid = l.sid and
   l.type = 'TX' and
   l.lmode = 6 and
   trunc(l.id1/65536) = r.usn and
   s.username <> ' ' and
   s.paddr = p.addr
order by 5, 6;

Refer this too>>>

What are deadlocks?

A deadlock is the situation where you have two, or more, Oracle "sessions" (well, transactional "states") competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.

There are a few reasons why your application may experience deadlocks, most of which are about application design. However, there are a few situations when, due to certain architectural design decisions, you may experience deadlocks simply due to the internal mechanisms of Oracle itself.

The "classic" deadlock

If session 1 is locking row 1, session 2 locks row 2, then session 1 attempts to lock row 2 (which will block since session 2 has the lock on that row), and then session 2 attempts to lock row 1 (which will block since session 1 has the lock on that row), then session 1 is waiting for session 2, and session 2 is waiting on session 1, which of course will never be resolved.

Here's an actual Oracle example. First of all, setup the environment :

SQL> CREATE TABLE t ( a varchar2(10) );

Table created.

SQL> insert into t values ( 'x' );

1 row created.

SQL> insert into t values ( 'y' );

1 row created.

SQL> commit;

Commit complete.

Now, session 1 attempts to delete row 'x', but doesn't commit :

SESS1> delete from t where a = 'x';

1 row deleted.

Okay, now session 2 attempts to delete row 'y', again, not committing :

SESS2> delete from t where a = 'y';

1 row deleted.

Now, since session 2 has not committed the delete against row 'y', session 1 can still "see" it (and attempt to delete it) :

SESS1> delete from t where a = 'y';

However, this will be blocked by session 2 (and hence wait), since row 'y' is locked, so session 1 is holding the lock on row 'x' but is waiting for row 'y'. Now, session 2 attempts to do it the "other way around", i.e. attempting to delete row 'x', while it retains the lock on row 'y' :

SESS2> delete from t where a = 'x';

this will be blocked by session 1 (and hence wait). So, the situation is tied, session 2 is waiting for session 1 and session 1 is waiting for session 2.

So, in reality, what happens in this situation? Well, obviously deadlocks just simply can't be allowed to occur, it's basically an infinite loop, the situation can't be resolved. So, Oracle steps in, automatically detecting the deadlock and resolving it by causing one of the sessions (can't actually guarantee which one) to receive an ORA-00060 error, i.e.

ORA-00060: deadlock detected while waiting for resource

What happens to both sessions?

So, we've seen what happens in terms of the deadlock, one of the sessions will error with the ORA-00060 error, but what about the other one? Well, this may be surprising to some people, but it carries on waiting, because the ORA-00060 error in the other session does not commit or rollback, and hence it retains it's previous lock(s), including those involved in the deadlock scenario. It is the responsibility of the client to commit or rollback if an ORA-00060 error is encountered.

So, what's the fix?

As with all deadlock scenarios, it's about avoidance rather than remedy. For the classic deadlock situation, you have to ensure that sessions do their operations in the same "order", i.e. if a process updates table t1 then t2, that any other process does so in the same order.

Deadlocks with Autonomous Transactions

As mentioned in the previous section, all deadlocks are variations on the same theme, i.e. multiple "sessions" competing for mutually locked resources. Since an Autonomous Transaction (AT) is basically a "session within a session", it's perfectly possible for it to occur here as well. This is maybe a little less obvious, since some people think that somehow they're part of the same "parent" session, but this is not the case. Note, however, that a deadlock is slightly more subtle when using ATs :

Showing this is trivial. First, create an AT process :

SQL> CREATE OR REPLACE PROCEDURE p_at
  2  AS
  3  PRAGMA AUTONOMOUS_TRANSACTION;
  4  BEGIN
  5    DELETE FROM t;
  6    COMMIT;
  7  END p_at;
  8  /

Procedure created.

Now, put a single row into a table :

SQL> insert into t values ( 'x' );

1 row created.

SQL> commit;

Commit complete.

Now, delete the row in the "parent" session :

SQL> delete from t ;

1 row deleted.

Now, since the "parent" session is locking the row in "t", any attempt to delete it via an AT from within the "parent" session, will result in ORA-00060, i.e.

SQL> exec p_at;
BEGIN p_at; END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "MARTIN.P_AT", line 5
ORA-06512: at line 1

So, why does the AT simply not block on the 'x' row? Well, if it did, imagine the situation, "parent" session is waiting for the AT to "finish" (like any other PL/SQL call), but it never will since it's blocked waiting for it's parent session to commit or rollback.

Deadlocks with Bitmap Indexes

Bitmap indexes were designed to be used solely within data warehouses, i.e. where the vast majority of the database activity is reading data, and there's very little (or no) data modification, except for batch processes which occasionally re-populate the warehouse. Each "row" in the bitmap index contains references to potentially many different rowids, in contrast to a B*-tree index which references a single rowid. It should be obvious, therefore, that, since the transactional mechanism is the same for all database operations, that any DML on a table which impacts the bitmap index may end up locking (or attempting to lock) many different "rows" within the index. This is the key concept with deadlocks in bitmap indexes, you're not being deadlocked on the underlying table, but on the index blocks.

The ability of an INSERT statement to deadlock (which is usually impossible) is therefore explained in that the bitmap index is maintaining other rows as a result of the DML.

Fortunately, it's easy to detect these kind of deadlocks, because the trace file has a very particular format :

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00080027-0000d2a1        12      37     X             15      35           S
TX-000a0016-0000d6d2        15      35     X             12      37           S
session 37: DID 0001-000C-00000002  session 35: DID 0001-000F-00000002
session 35: DID 0001-000F-00000002  session 37: DID 0001-000C-00000002
Rows waited on:
Session 35: no row
Session 37: no row

Note, the "no row" entries. This is the prime indicator that this is a bitmap index deadlock. There's very little you can do about this, apart from not use bitmap indexes in a DML environment.

Deadlocks with unindexed foreign keys

There are various attributes which can be specified when creating foreign key constraints. The ones of interest are UPDATE and DELETE CASCADE. These attributes define what happens to the child record(s) when various actions are performed on the parent record.

The upshot of the deadlock problem is that in these situations (i.e. using the UPDATE or DELETE CASCADE options of the FK constraint), without an index on the foreign key column(s), Oracle has no option but to attempt to get a table lock on the child table while it performs it's maintenance. In this situation, it's obvious that the potential for deadlocking is vastly increased for having to lock the whole child table.

Fortunately, resolving this is quite straightforward, you simply put an index on the foreign key column(s).

So, is there any way of being proactive about this, and identifying the table(s) / column(s) which are the subject of a foreign key constraint, but do not have indexes? Yes, Tom Kyte has written one and it's located here.

For more information about this problem (and other performance related issues regarding unindexed FKs), see http://asktom.oracle.com/~tkyte/unindex/index.html.

Detecting and identifying deadlocks

Well, the obvious way of detecting a deadlock is when you see the ORA-00060 error, however, sometimes ( especially with complex applications ), it may not be obvious what is actually causing the deadlock itself. Fortunately, whenever a deadlock occurs, Oracle creates a trace file (in user_dump_dest), with information which will allow you to see details about the deadlock, what the shared resource is, what the current SQL statement was, etc. etc.

Here's an extract from the trace file generated on a 10g Express Edition (XE) from the above first deadlock situation above. Note, the full trace file contains a lot more information for the developer / DBA to utilise to determine the exact cause of the situation which caused the deadlock.

Trace file extract from two distinct Oracle sessions

*** 2006-11-05 13:29:16.921
*** ACTION NAME:() 2006-11-05 13:29:16.906
*** MODULE NAME:(SQL*Plus) 2006-11-05 13:29:16.906
*** SERVICE NAME:(SYS$USERS) 2006-11-05 13:29:16.906
*** SESSION ID:(27.5) 2006-11-05 13:29:16.906
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
delete from t where a = 'x'
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070023-0000009c        18      27     X             20      38           X
TX-00060021-0000008d        20      38     X             18      27           X
session 27: DID 0001-0012-00000011  session 38: DID 0001-0014-00000005
session 38: DID 0001-0014-00000005  session 27: DID 0001-0012-00000011
Rows waited on:
Session 38: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAB
  (dictionary objn - 13646, file - 4, block - 15, slot - 1)
Session 27: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAA
  (dictionary objn - 13646, file - 4, block - 15, slot - 0)

Note, the fragment (dictionary objn - 13646, file - 4, block - 15, slot - 1), the objn - 13646 relates to the object_id, which can be queried via dba_objects or similar.

Trace file extract for same session with Autonomous Transaction

*** 2006-11-05 14:04:20.328
*** ACTION NAME:() 2006-11-05 14:04:20.328
*** MODULE NAME:(SQL*Plus) 2006-11-05 14:04:20.328
*** SERVICE NAME:(SYS$USERS) 2006-11-05 14:04:20.328
*** SESSION ID:(27.26) 2006-11-05 14:04:20.328
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
DELETE FROM T
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
209DD638         5  procedure MARTIN.P_AT
1D1C566C         1  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00080003-00000091        18      27     X             18      27           X
session 27: DID 0001-0012-0000001E  session 27: DID 0001-0012-0000001E
Rows waited on:
Session 27: obj - rowid = 0000354E - AAADVOAAEAAAAAPAAA
  (dictionary objn - 13646, file - 4, block - 15, slot - 0)

As you can see, Oracle does a good job of giving as much information about the involved sessions (including "current" SQL statement, etc.).

Question: I'm having trouble running a parallel DML statement. Here is the code for Parallel DML, I'm running only one session which is current. I don't know how comes I got an error saying DEADLOCK DETECTED:

SQL> alter session enable parallel dml;

Session altered.

SQL>
SQL> alter session set db_file_multiblock_read_count=128;

Session altered.

SQL> set timin on
SQL>
SQL>
SQL> UPDATE /*+ full(myobjects) parallel(myobjects, 8)*/ myobjects
2 SET object_name = upper(object_name);
update /*+ full(myobjects) parallel(myobjects, 8)*/
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P015
ORA-00060: deadlock detected while waiting for resource

Answer: It's one of two issues:

Deadly embrace - Competing DML tasks cause perpetual deadlock
ITL shortage - More ITL's needed for parallel DML

ORA-00060 and Competing Updates

Do you have competing updates running? This may be an age-old issue known as the perpetual embrace! The docs note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

If your ORA-00060 is caused by competing resources, the perpetual embracehappens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

ORA-00060 and INITRANS Shortage

Also, Arup Nanda has scripts for detecting ITL waits:

More Explainations:

ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

ORA-00060 error indicates that a dead lock occurred due to a resource contention with another session and Oracle rolled back your current statement to resolve the dead lock. The other session can proceed further as usual . Your current sessions rolled backed statements needs to be resubmitted for the execution after the required resources are available.

These dead locks can occur in different scenarios: They can occur while doing parallel DML operations, while updating/deleting data from same tables from different sessions , while performing transactions on bitmap index tables and so on but the mentioned scenarios are the most common ones.

At this point I think we would like to explore more about dead locks, so here are the in depth details on dead locks:

What is dead lock, when it occurs and how to fix it?

Dead lock occurs when two or more users waiting for the resources locked by each other. The users are stuck and they can not proceed as they may end up waiting indefinitely for the resources form each other. So when this condition occurs these users are stuck (deadly embraced) and cannot continue processing.

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

Most commonly these deadlocks are caused by the applications that involve multi table updates in the same transaction and multiple applications/transactions are acting on the same table at the same time. These multi-table deadlocks can be avoided by locking tables in same order in all applications/transactions, thus preventing a deadlock condition.

In the following example I am demonstrating a dead lock scenario . In the first session ie. session1 lock table: CUSTOMER then CONTACT; and in session2: CONATCT then CUSTOMER.

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

Session 1 lock table CUSTOMER:
SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';

1 row updated.

Session 2 lock table CONTACT:

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

1 row updated.

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

SQL> update contact set language_id = 8 where customer_ref = '10000000000000000486';

Session 2 now update CUSTOMER, causing the deadlock:

SQL> update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486';
update customer set concatenate_bills_boo = 'T' where customer_ref = '10000000000000000486'
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

SQL>

Resolution/Fix for deadly Embrace Dead Lock:

The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.

In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.

Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.

To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.

In order to find the lock information from the database on which objects / tables have locked currently, which are the blocking sessions and users, ,you can find lot of useful information and scripts at ORA-00054 resource busy errorarticle.

ITL shortage deadlocks :

ITL shortage deadlocks are those dead locks ORA-00060 errors that occur during the execution of UPDATE and DELETE statements where two processes wait for 'S' mode locks on each other's 'TX' enqueues. 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.

Resolution/Fix for ITL Dead locks:

To resolve ITL dead locks , 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.

Monitoring ITL waits :

Query the sys.v_$segment_statistics view for identifying and monitoring ITL waits per segments

Other causes for Dead Locks:

Bitmap indexes and dead locks:

High transaction activity on tables with bitmap indexes often causes dead locks. Bitmap indexes are only appropriate in read only/ read mostly environments.

Resolution to Bitmap Index dead locks:

This can be resolved by setting a very high INITTRANS value for the bitmap index but most cases it would be better & best to disable the index before heavy transactional activity, and to rebuild it after heavy transactional activity is done.

Additional dead lock related information :

Bitmap dead lock related info:

You can find more details on bitmap indexes and how they trigger dead locks atasktom by Thomas Kyte.

ITL Waits :

The following is very insightful article on ITL by Arup Nanda with some scripts for identifying the ITL waits.. Originally I thought to provide a link to this article but later I felt this article is very important , informative so I didn’t want the readers to miss/lose this info if that link changed by chance. So I am providing the article inline here with a sole purpose of making the freely available information on the internet in a readily usable form.

Here is the article on ITL:

“Oracle Interested Transaction List (ITL) Waits
by Arup Nanda
Author of Oracle Privacy Security Auditing

What is ITL? Ever wondered how Oracle locks rows on behalf of transactions? In some RDBMS vendor implementations, a lock manager maintains information on which row is locked by which transaction. This works great in theory, but soon the lock manager becomes a single point of contention, as each transaction must wait to get a lock from the manager and then wait again to release the lock.

This severely limits the scalability of the applications. In fact, application developers of some RDBMS products despise holding locks for a long time, and often resort to a full table lock when all that's needed is to get a few rows locked. This creates further waits, and consequently, scalability suffers.

So how is that different in Oracle? For starters, there is no lock manager. When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager. This makes applications immensely scalable.

So, what portion of the block header contains information on locking? It is a simple data structure called "Interested Transaction List" (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row (hence the name "Interested Transaction List").

When the same transaction or another one locks another row, the information is stored in another slot, and so on. After a transaction ends via commit or a rollback, the locks are released and so are the slots that were used to mark the blocks, and these newly freed slots are reused for the other transactions. So there is in fact a queue, but it's at a block level, not at the entire database level or even at a segment level.

The next logical question that comes up is, how many slots are typically available? During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.

Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.

So, what happens when a transaction does not find a free slot to place its lock information? This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue." Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty.

Then another transaction, Txn2, updates the row Row2 and wants to lock the row. However, there are no more slots in the ITL available to service the transaction. The MAXTRANS entry is 11, meaning the ITL can grow up to 11 slots and the block has empty space. Therefore, ITL can grow by another slot and Slot number two is created and allocated to Txn2 (refer to figure 4).

Now the empty space in the block is severely limited, and it will not be able to fit another ITL slot. If at this time another transaction comes in to update the row three, it must have a free slot in the ITL. The MAXTRANS is 11 and currently only two slots have been created, so another one is possible; but since there is no room in the block to grow, the slot can't be created. Therefore, the Txn3 has to wait until either of the other transactions rolls back or commits and the slot held by it becomes free. At this time the session will experience an ITL waits event as seen from the view V$SESSION_WAIT.

To better illustrate the concept, let's illustrate such waits using a case. Create the following table and then populate it with several rows. Note MAXTRANS value.
CREATE TABLE TAB1
( COL1 NUMBER,
COL2 VARCHAR2(200))
INITRANS 1 MAXTRANS 1
/

DECLARE
I NUMBER;
BEGIN
FOR I IN 1..10000 LOOP
INSERT INTO TAB1 VALUES
(I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');
END LOOP;
COMMIT;
END;
/

Now update a row of the table from one session, but do not commit it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;

From another session, update row number two and do not update it.
UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;

This session will wait. Why? It's updating a row for COL1 = 2, not the same row updated in the other session for COL1 = 1. So why is the session waiting? It's because the first transaction occupied the only available ITL slot. The second transaction needed another slot to place its lock information, but since the MAXTRANS I defined is one, the ITL could not grow to create another slot. Thus, the latter transaction has to wait until the former session releases the lock and makes the slot available. Now increase the MAXTRANS of the table by issuing

ALTER TABLE TAB1 MAXTRANS 11;

and redo the above test. The second session will not wait this time because the ITL had enough free slots for both transactions. How to Reduce ITL WaitsThe primary cause of ITL waits is that free slots in the ITL are not available. This can be due to low setting of the MAXTRANS, which places a hard limit on the number of transactions that can have locks on a block or, the block is so packed that there is no room for the ITL to grow OR or both.

Therefore, setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL. However, doing so also means that there is less space in the block for actual data, increasing wasted space.

The other option is to making sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL. This can be done by increasing PCTFREE, increasing FREELISTS and FREELIST GROUPS parameters for a table.

This will make a block hold less data and more room for the ITL to grow. As a direct result of the reduction in packing, the table will experience fewer buffer busy wait events, and performance will be increased.
How to Diagnose the ITL WaitHow do you know that a segment is experiencing ITL waits? The best answer will be found in the Segment Level Statistics provided in Oracle9i Release 2. To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.

SELECT
OWNER,
OBJECT_NAME
FROM
V$SEGMENT_STATISTICS
WHERE
STATISTIC_NAME = 'ITL waits'
AND
VALUE > 0;

This unearths the objects that were subjected to ITL waits since the start up of the instance. Note that this view resets when the instance bounces. (For a more detailed explanation of this view and how to set it up, please refer to the article by this author here in DBAzine.)
In versions earlier than 9i, checking for ITL waits is tricky.

When you suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query:

Select s.sid SID,
s.serial# Serial#,
l.type type,
' ' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,
s.serial# Serial#,
l.type type,
object_name object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select s.sid SID,
s.serial# Serial#,
l.type type,
'(Rollback='||rtrim(r.name)||')' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6
/

The output of the query will look something like this:

SID SERIAL# TY OBJECT_NAM HELD REQUEST
----- ------- -- ---------- ---------- --------
36 8428 TX 0 4
36 8428 TM TAB1 3 0
52 29592 TM TAB1 3 0
52 29592 TX (Rollback=RBS1_6) 6 0

Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, you can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, you would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen.

Needless to say, this is a rather convoluted and inaccurate way to diagnose the ITL waits in pre-Oracle9i Release 2 versions.

What INITRANS Value is Optimal? Conversely, how do you know if the INITRANS setting is too high and the space is just being wasted? Ascertaining this is possible by using a few random block dumps from the segment in question. First, find out the header file# and header block# of the segment by issuing the following query:

SELECT HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTSWHERE OWNER = '...'AND SEGMENT_NAME = '...';

Use the output of the query to do a block dump of the header block.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce a trace file in the USER_DUMP_DESTINATION directory. Open the trace file and find out the section on extent control via the following:
Extent Control Header

-----------------------------------------------------------------

Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 10

last map 0x00000000 #maps: 0 offset: 2080

Highwater:: 0x02011f87 ext#: 0 blk#: 0 ext size: 10

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 0

Unlocked

Map Header:: next 0x00000000 #extents: 1 obj#: 53689 flag: 0x40000000

Extent Map

-----------------------------------------------------------------

0x02011f87 length: 10

Find out the real number of blocks for the segment from dba_segments via the following:
SELECT BLOCKS FROM DBA_SEGMENTS

WHERE OWNER = '...' AND SEGMENT_NAME = '...';

Say this returns 12, and the #blocks shows 10; this means the first two blocks are header blocks; the data starts at the third block. Take a dump of the third block, which is obtained by adding two to the header block# obtained above.
ALTER SYSTEM DUMP DATAFILE BLOCK MIN BLOCK MAX ;

This will produce another trace file in the USER_DUMP_DEST directory. If you issued it during the same session as above, then the trace will be written in the trace file opened earlier. Open the file and locate the following section.
buffer tsn: 8 rdba: 0x02011f88 (8/73608)

scn: 0x0000.389b8d81 seq: 0x01 flg: 0x04 tail: 0x8d810601

frmt: 0x02 chkval: 0x2ef5 type: 0x06=trans data

Block header dump: 0x02011f88

Object id on Block? Y

seg/obj: 0xd1ad csc: 0x00.389b8d81 itc: 4 flg: - typ: 1 - DATA

fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0003.003.000024cc 0x00804067.050a.13 C-U- 0 scn 0x0000.389b304e

0x02 0x0007.010.00002763 0x00801f49.0453.01 C--- 0 scn 0x0000.389b2628

0x03 0x0002.00a.000025d5 0x00804d42.04b2.25 C--- 0 scn 0x0000.389b2811

0x04 0x0006.006.00002515 0x00800962.03c8.18 CU 0 scn 0x0000.389b3044

This shows some very important information on the block, especially in the ITL section shown above. This table has an INITRANS entry of four, so there are four lines, one each per the ITL. The Flag column above the flag -U- indicates that the particular ITL was used. In this case, only two of the ITLs were used, and the other two were never used. However, this is the case for this block only. By selecting block dumps from other randomly selected blocks, you could have an idea how many ITLs are actually used. Then you may decide to reduce the INITRANS.

Automatic Block Management in Oracle9iIn Oracle9i, the process of space mangement inside a block is somewhat changed due to the introduction of the Automatic Block Management (ABM) feature, also known as Automatic Segment Space Management (ASSM). The option is specified at the tablespace level in the storage parameter as SEGMENT SPACE MANAGEMENT AUTO. For instance, the tablespace TS1 can be created as

CREATE TABESPACE TS1
DATAFILE '...'
EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;The last line of this code does the magic. In the ABM mode, Oracle maintains a bitmap for each segment with the information on the block. A bitmap is a data structure with a bit representing each block. When a block becomes available for INSERT, the information is made available simply by setting the corresponding bit in the bitmap rather than using freelists.

So, what does this have to do with ITL waits? The very cause of ITL waits is not freespace management, but the unavailability of a slot in ITL waits. So you still have to look for ITL waits and correct them using INITRANS and MAXTRANS. In fact, the problem may become exacerbated because the block becomes quite packed following an efficient space management system, and that may lead to lack of space for ITL growth. You can prevent this by keeping a large INITRANS for the segment.

Proper setting of INITRANS and MAXTRANS and packing of the blocks is vital to avoid ITL waits in Oracle. It's interesting to note that locking doesn't cause waits, but rather, the mechanism for locking as well as and poor planning. However, the good news is that this situation can be easily fixed by reorganizing the table and adding more slots to the Interested Transaction List.”

CONCLUSION:

Dead locks do occur in most of the applications and dead locks can be avoided by properly designing the transactions and applications by keeping other transactions and applications in mind. ITL waits and dead locks related to ITL waits can be avoided by setting of INITRANS and MAXTRANS properly. Dead locks during the transactions on bitmap indexed tables can be avoided by performing heavy transactions with no bitmap indexes and after completing the transactions rebuild the bitmap indexes.

ORA-00060: deadlock detected while waiting for resource

ORA-00936:missing expression

ORA-02063 remote db error and how to fix it

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-04091: table is mutating trigger

ORA-00604: error occurred at recursive SQL level string

PLS-00222: no function with name exists

ORA-00600: internal error code arguments:

ORA-21000:error number argument to raise_application_error is out of range

ORA-00257: archiver error. Connect internal only, until freed.

ORA-00001: unique constraint

ORA-00017: session requested to set trace event