Oracle Locking Survival Guide

Overview
Inmulti-user systems, many users may update the same information at the same time. Locking allows
only one user to update a particular data block; another person cannot modify the same data.

The basic idea of locking is that when a user modifies data through a transaction, that data is locked
by that transaction until the transaction is committed or rolled back. The lock is held until the
transaction is complete - this known as data concurrency.

The second purpose of locking is to ensure that all processes can always access (read) the original
data as they were at the time the query began (uncommited modification), This is known as read
consistency.

Although locks are vital to enforce database consistency, they can create performance problems.
Every time one process issues a lock, another user may be shut out from processing the locked row or
table. Oracle allows to lock whatever resources you need - a single row, many rows, an entire table,
even many tables. But the larger the scope of the lock, the more processes you potentially shut out.

Oracle provides two different levels of locking: Row Level Lock and Table Level Lock.

Row-Level Locking

With a row-level locking strategy, each row within a table can be locked individually. Locked rows
can be updated only by the locking process. All other rows in the table are still available for updating
by other processes. Of course, other processes continue to be able to read any row in the table,
including the one that is actually being updated. When other processes do read updated rows, they see
only the old version of the row prior to update (via a rollback segment) until the changes are actually
committed. This is known as a consistent read.

When a process places a row level lock on a record, what really happens?

1. First, a data manipulation language (DML) lock is placed over the row. This lock
prevents other processes from updating (or locking) the row. This lock is released only
when the locking process successfully commits the transaction to the database (i.e.,
makes the updates to that transaction permanent) or when the process is rolled back.

2. Next, a data dictionary language (DDL) lock is placed over the table to prevent
structural alterations to the table. For example, this type of lock keeps the DBA from
being able to remove a table by issuing a DROP statement against the table. This lock
is released only when the locking process successfully commits the transaction to the
 database or when the process is rolled back.

Table-Level Locking

With table-level locking, the entire table is locked as an entity. Once a process has locked a table,
only that process can update (or lock) any row in the table. None of the rows in the table are available
for updating by any other process. Of course, other processes continue to be able to read any row in
the table, including the one that is actually being updated.

How does table-level locking work?

The first DML operation that needs to update a row in a table obtains what's called a Row Share
Exclusive lock over the entire table. All other query-only processes needing access to the table are
informed that they must use the rollback information for the locking process. The lock is released
only when the locking process successfully commits the transaction to the database or when the
process is rolled back.

Releasing Locks

Many users believe that they are the only users on the system - at least the only ones who count.
Unfortunately, this type of attitude is what causes locking problems. We've often observed
applications that were completely stalled because one user decided to go to lunch without having
committed his or her changes. Remember that all locking (row or table) will prevent other users from
updating information. Every application has a handful of central, core tables. Inadvertently locking
such tables can affect many other people in a system.

Many users, and some programmers, don't understand that terminating a process does not always
release locks. Switching off your workstation before you go home does not always release locks.
Locks are released only when changes are committed or rolled back. A user's action is the only thing
that distinguishes between committing, aborting, and rolling back changes. Make it a priority to train
your users to commit or roll back all outstanding changes before leaving their current screens.

Modes of Locking
Oracle uses two modes of locking in a multi-user database:

 Exclusive lock mode (X) prevents the associates resource from being shared. This
lock mode is obtained to modify data. The first transaction to lock a resource
exclusively is the only transaction that can alter the resource until the exclusive lock is
released.

 Share lock mode (S) allows the associated resource to be shared, depending on the
operations involved. Multiple users reading data can share the data, holding share
locks to prevent concurrent access by a writer (who needs an exclusive lock). Several
 transactions can acquire share locks on the same resource.

Exclusive Locks

SQL Statement Mode of Lock

SELECT ... FROM table... No Lock
INSERT INTO table ... RX
UPDATE table ... RX
DELETE FROM table ... RX
LOCK TABLE table IN ROW EXCLUSIVE MODE RX
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE SRX
LOCK TABLE table IN EXCLUSIVE MODE X

Share Locks

SQL Statement Mode of Lock

SELECT ... FROM table FOR UPDATE OF ... RS
LOCK TABLE table IN ROW SHARE MODE RS
LOCK TABLE table IN SHARE MODE S
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE SRX

RS: Row Share

RX: Row Exclusive
S: Share
SRX: Share Row Exclusive
X: Exclusive

Description of each Lock Mode

The following sections explain each mode of lock, from least restrictive to most restrictive.

Row Share Table Locks (RS)

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction
holding the lock on the table has locked rows in the table and intends to update them. A row share
table lock is automatically acquired for a table when one of the following SQL statements is
executed:

SELECT ... FROM table ... FOR UPDATE OF ... ;

LOCK TABLE table IN ROW SHARE MODE;

A row share table lock is the least restrictive mode of table lock, offering the highest degree of
concurrency for a table.

Permitted Operations:

A row share table lock held by a transaction allows other transactions to:

SELECT (query the table)

INSERT, UPDATE, DELETE
or lock rows concurrently in the same table.

Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row
exclusive table locks for the same table.

Prohibited Operations:

A row share table lock held by a transaction prevents other transactions from exclusive write access
to the same table.

When to Lock with ROW SHARE Mode:

Your transaction needs to prevent another transaction from acquiring an intervening share, share row,
or exclusive table lock for a table before the table can be updated in your transaction. If another
transaction acquires an intervening share, share row, or exclusive table lock, no other transactions can
update the table until the locking transaction commits or rolls back.

Your transaction needs to prevent a table from being altered or dropped before the table can be
modified later in your transaction.

Example

We use the EMP table for the next examples.

EMPNO ENAME JOB

---------- ---------- ---------
7369 Smith CLERK
7499 Allen SALESMAN
7521 Ward SALESMAN
7566 Jones MANAGER
7654 Martin SALESMAN
 7698 Blake MANAGER
7782 Clark MANAGER
7788 Scott ANALYST
7839 King PRESIDENT
7844 Turner SALESMAN
7876 Adams CLERK
7900 James TEST
7902 Ford ANALYST
7934 Miller CLERK

Session 1 Session 2

select job from emp select job from emp

where job = 'CLERK' where job = 'CLERK'
for update of empno; for update of empno;
OK Waiting ....

select job from emp

where job = 'MANAGER'
for update of empno;
OK

lock table emp in share mode;

OK

lock table emp in exclusive mode;

Waiting ....

insert into emp (empno,ename)

values (9999,'Test');
OK

delete from emp where empno = 9999;

OK

delete from emp where empno = 7876;

Waiting .... (Blocked by Session 1)

update emp set job = 'CLIMBER'

where empno = 7876;
Waiting .... (Blocked by Session 1)

A first look about the locking situation can be found in DBA_LOCKS

SELECT SUBSTR(TO_CHAR(session_id),1,5) "SID",

SUBSTR(lock_type,1,15) "Lock Type",
 SUBSTR(mode_held,1,15) "Mode Held",
SUBSTR(blocking_others,1,15) "Blocking?"
FROM dba_locks
/

SID Lock Type Mode Held Blocking?

----- --------------- --------------- ---------------
95 Transaction Exclusive Blocking <- This is Session 1
95 DML Row-S (SS) Not Blocking
98 DML Row-X (SX) Not Blocking <- This is Session 2
98 Transaction None Not Blocking
110 Temp Segment Row-X (SX) Not Blocking
111 RS Row-S (SS) Not Blocking
111 Control File Row-S (SS) Not Blocking
111 XR Null Not Blocking
112 Redo Thread Exclusive Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 Media Recovery Share Not Blocking
113 PW Row-X (SX) Not Blocking

Row Exclusive Table Locks (RX)

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the
transaction holding the lock has made one or more updates to rows in the table. A row exclusive table
lock is acquired automatically for a table modified by the following types of statements:

INSERT INTO table ... ;

UPDATE table ... ;
DELETE FROM table ... ;
LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations:

A row exclusive table lock held by a transaction allows other transactions to

SELECT (query the table)

INSERT, UPDATE, DELETE
or lock rows concurrently in the same table.

Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive
and row share table locks for the same table.

Prohibited Operations:

A row exclusive table lock held by a transaction prevents other transactions from manually locking
the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the
table using the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

When to Lock with ROW EXCLUSIVE Mode:

This is the Default Locking Behaviour of Oracle.

Example

Session 1 Session 2

update emp lock table emp in exclusive mode;

set ename = 'Zahn'; Waiting ....
OK

Share Table Locks (S)

A share table lock is acquired automatically for the table specified in the following statement:

LOCK TABLE table IN SHARE MODE;

Permitted Operations:

A share table lock held by a transaction allows other transactions only to

to SELECT (query the table)

to lock specific rows with SELECT ... FOR UPDATE
or to execute LOCK TABLE ... IN SHARE MODE

statements successfully. No updates are allowed by other transactions. Multiple transactions can hold
share table locks for the same table concurrently. In this case, no transaction can update the table
(even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE
clause). Therefore, a transaction that has a share table lock can update the table only if no other
transactions also have a share table lock on the same table.

Prohibited Operations:

A share table lock held by a transaction prevents other transactions from modifying the same table
and from executing the following statements:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;
LOCK TABLE table IN ROW EXCLUSIVE MODE;

When to Lock with SHARE Mode

Your transaction only queries the table, and requires a consistent set of the table's data for the
duration of the transaction.

You can hold up other transactions that try to update the locked table, until all transactions that hold
SHARE locks on the table either commit or roll back.

Other transactions may acquire concurrent SHARE table locks on the same table, also allowing them
the option of transaction-level read consistency.

Caution:

Your transaction may or may not update the table later in the same transaction. However, if multiple
transactions concurrently hold share table locks for the same table, no transaction can update the table
(even if row locks are held as the result of a SELECT... FOR UPDATE statement). Therefore, if
concurrent share table locks on the same table are common, updates cannot proceed and deadlocks
are common. In this case, use share row exclusive or exclusive table locks instead.

Example 1

Session 1 Session 2

lock table emp select * from emp;

in share mode; OK
OK

update emp This and other Transactions have to wait

set ename = 'Zahn' until Session 1 commits the Transaction.
where empno = 7900;
commit;
OK
This and other Transactions can
lock table emp get a Share Lock (Lock Switch).
in share mode;
OK
lock table emp
This and other Transactions have to wait in share mode;
until Session 2 commits the Transaction. OK

update emp
set ename = 'Müller'
where empno = 7900;
Waiting ....

Example 2

For example, assume that two tables, emp and budget, require a consistent set of data in a third table,
dept. For a given department number, you want to update the information in both of these tables, and
ensure that no new members are added to the department between these two transactions.

Although this scenario is quite rare, it can be accommodated by locking the dept table in SHARE
MODE, as shown in the following example. Because the dept table is rarely updated, locking it
probably does not cause many other transactions to wait long.

LOCK TABLE dept IN SHARE MODE; /* Other Transactions have to wait */

UPDATE emp
SET sal = sal * 1.1
WHERE deptno IN
(SELECT deptno FROM dept WHERE loc = 'DALLAS');

UPDATE budget
SET Totsal = Totsal * 1.1
WHERE deptno IN
(SELECT deptno FROM dept WHERE Loc = 'DALLAS');

COMMIT; /* This releases the lock */

Exclusive Table Locks (X)

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that
holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as
follows:

LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations:
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits
other transactions only to query the table.

Prohibited Operations:

An exclusive table lock held by a transaction prohibits other transactions from performing any type of
DML statement or placing any type of lock on the table.

Be careful to use an EXCLUSIVE lock!

Your transaction requires immediate update access to the locked table. When your transaction holds
an exclusive table lock, other transactions cannot lock specific rows in the locked table.

Your transaction also ensures transaction-level read consistency for the locked table until the
transaction is committed or rolled back.

You are not concerned about low levels of data concurrency, making transactions that request
exclusive table locks wait in line to update the table sequentially.

Example

Session 1 Session 2

lock table emp select * from emp;

in exclusive mode; OK
OK

update emp This and other Transactions have to wait

set ename = 'Zahn' until Session 1 commits the Transaction.
where empno = 7900;
commit;
OK
This and other Transactions cannot
lock table emp get any other Lock (No Lock Switch).
in exclusive mode;
OK lock table emp
in share mode;
Waiting ....

lock table emp

in exclusive mode;
Waiting ....

update emp
set ename = 'Zahn'
 where empno = 7900;
Waiting ....

Data Lock Conversion Versus Lock Escalation

A transaction holds exclusive row locks for all rows inserted, updated, or deleted within the
transaction. Because row locks are acquired at the highest degree of restrictiveness, no lock
conversion is required or performed.

Oracle automatically converts a table lock of lower restrictiveness to one of higher restrictiveness as
appropriate. For example, assume that a transaction uses a SELECT statement with the FOR
UPDATE clause to lock rows of a table. As a result, it acquires the exclusive row locks and a row
share table lock for the table. If the transaction later updates one or more of the locked rows, the row
share table lock is automatically converted to a row exclusive table lock.

Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows)
and a database raises the locks to a higher level of granularity (for example, table). For example, if a
single user locks many rows in a table, some databases automatically escalate the user's row locks to
a single table. The number of locks is reduced, but the restrictiveness of what is being locked is
increased.

Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks.

Deadlocks
A deadlock can occur when two or more users are waiting for data locked by each other. Deadlocks
prevent some transactions from continuing to work. The next example illustrates two transactions in a
deadlock.

Example

Session 1 Session 2 Time

update emp set update emp set A

sal = sal * 1.1 mgr = 1342
where empno = 7369; where empno = 7934;

1 row updated. 1 row updated.

update emp set update emp set B

sal = sal * 1.1 mgr = 1342
 where empno = 7934; where empno = 7369;

ERROR at line 1: Waiting ... C

ORA-00060: deadlock detected while
waiting for resource

In the example, no problem exists at time point A, as each transaction has a row lock on the row it
attempts to update. Each transaction proceeds without being terminated. However, each tries next to
update the row currently held by the other transaction. Therefore, a deadlock results at time point B,
because neither transaction can obtain the resource it needs to proceed or terminate. It is a deadlock
because no matter how long each transaction waits, the conflicting locks are held.

Automatic Deadlock Detection

Oracle performs automatic deadlock detection for enqueue locking deadlocks. Deadlock detection is
initiated whenever an enqueue wait times out, if the resource type required is regarded as deadlock
sensitive, and if the lock state for the resource has not changed. If any session that is holding a lock
on the required resource in an incompatible mode is waiting directly or indirectly for a resource that
is held by the current session in an incompatible mode, then a deadlock exists.

If a deadlock is detected, the session that was unlucky enough to find it aborts its lock request and
rolls back its current statement in order to break the deadlock. Note that this is a rollback of the
current statement only, not necessarily the entire transaction. Oracle places an implicit savepoint at
the beginning of each statement, called the default savepoint, and it is to this savepoint that the
transaction is rolled back in the first case. This is enough to resolve the technical deadlock. However,
the interacting sessions may well remain blocked.

ORA-60 error in ALERT.LOG

An ORA-60 error is returned to the session that found the deadlock, and if this exception is not
handled, then depending on the rules of the application development tool, the entire transaction is
normally rolled back, and a deadlock state dump written to the user dump destination directory. This,
of course, resolves the deadlock entirely. The enqueue deadlocks statistic in V$SYSSTAT records the
number of times that an enqueue deadlock has been detected.

select name, value

from v$sysstat
where name = 'enqueue deadlocks';

NAME VALUE
------------------------------------------------------------ ----------
enqueue deadlocks 1

How to avoid Deadlocks

Application developers can eliminate all risk of enqueue deadlocks by ensuring that transactions
requiring multiple resources always lock them in the same order. However, in complex
applications, this is easier said than done, particularly if an ad hoc query tool is used. To be safe, you
should adopt a strict locking order, but you must also handle the ORA-60 exception appropriately.
In some cases it may be sufficient to pause for three seconds, and then retry the statement. However,
in general, it is safest to roll back the transaction entirely, before pausing and retrying.

Referential Integrity Locks (RI Locks)

With the introduction of automated referential integrity (RI) came a whole new suite of locking
problems. What seems at first to be a DBA's blessing can turn out to be an absolute nightmare when
the DBA doesn't fully understand the implications of this feature. Why is this so?

RI constraints are validated by the database via a simple SELECT from the dependent (parent) table
in question-very simple, very straightforward. If a row is deleted or a primary key is modified within
the parent table, all associated child tables need to be scanned to make sure no orphaned records will
result. If a row is inserted or the foreign key is modified, the parent table is scanned to ensure that the
new foreign key value(s) is valid. If a DELETE CASCADE clause is included, all associated child
table records are deleted. Problems begin to arise when we look at how the referential integrity is
enforced.

Oracle assumes the existence of an index over every foreign key within a table. This assumption is
valid for a primary key constraint or even a unique key constraint but a little presumptuous for every
foreign key.

Index or no Index on Foreign Key's ?

If an index exists on the foreign key column of the child table, no DML locks, other than a lock over
the rows being modified, are required.

If the index is not created, a share lock is taken out on the child table for the duration of the
transaction.

The referential integrity validation could take several minutes or even hours to resolve. The share
lock over the child table will allow other users to simultaneously read from the table, while restricting
certain types of modification. The share lock over the table can actually block other normal, everyday
modification of other rows in that table.

You can use the script: show_missing_fk_index.sql to check unindexed foreign keys:

SQL> start show_missing_fk_index.sql

Please enter Owner Name and Table Name. Wildcards allowed (DEFAULT: %)
eg.: SCOTT, S% OR %
eg.: EMP, E% OR %

Owner <%>: SCOTT

Tables <%>:

Unindexed Foreign Keys owned by Owner: SCOTT

Table Name 1. Column Constraint Name

------------------------ ------------------------ ---------------
EMP DEPTNO FK_EMP_DEPT

What is so dangerous about a Cascading Delete ?

Oracle allows to enhance a referential integrity definition to included cascading deletion. If a row is
deleted from a parent table, all of the associated children will be automatically purged. This behavior
obviously will affect an application's locking strategy, again circumnavigating normal object locking,
removing control from the programmer.

What is so dangerous about a cascading delete? A deleted child table might, in turn, have its own
child tables. Even worse, the child tables could have table-level triggers that begin to fire. What starts
out as a simple, single-record delete from a harmless table could turn into an uncontrollable torrent of
cascading deletes and stored database triggers.

DELETE CASCADE constraints can be found with the following script:

SQL> SELECT OWNER,

CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
DELETE_RULE
FROM USER_CONSTRAINTS
WHERE DELETE_RULE IS NOT NULL;

CONSTRAINT_NAME C TABLE_NAME DELETE_RU

------------------------------ - ----------------- ---------
FK_EMP_DEPT R EMP CASCADE

Blocking Locks
Oracle resolves true enqueue deadlocks so quickly that overall system activity is scarcely affected.
However, blocking locks can bring application processing to a standstill. For example, if a long-
running transaction takes a shared mode lock on a key application table, then all updates to that table
must wait.

There are numerous ways of attempting to diagnose blocking lock situations, normally with the
intention of killing the offending session.

Blocking locks are almost always TX (transaction) locks or TM (table) locks . When a session
waits on a TX lock, it is waiting for that transaction to either commit or roll back. The reason for
waiting is that the transaction has modified a data block, and the waiting session needs to modify the
same part of that block. In such cases, the row wait columns of V$SESSION can be useful in
identifying the database object, file, and block numbers concerned, and even the row number in the
case of row locks. V$LOCKED_OBJECT can then be used to obtain session information for the
sessions holding DML locks on the crucial database object. This is based on the fact that sessions
with blocking TX enqueue locks always hold a DML lock as well, unless DML locks have been
disabled.

It may not be adequate, however, to identify a single blocking session, because it may, in turn, be
blocked by another session. To address this requirement, Oracle's UTLLOCKT.SQL script gives a
tree-structured report showing the relationship between blocking and waiting sessions. Some DBAs
are loath to use this script because it creates a temporary table, which will block if another space
management transaction is caught behind the blocking lock. Although this is extremely unlikely, the
same information can be obtained from the DBA_WAITERS view if necessary. The DBA_WAITERS
view is created by Oracle's catblock.sql script.

Some application developers attempt to evade blocking locks by preceding all updates with a
SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE SKIP LOCKED statement.
However, if they allow user interaction between taking a sub-exclusive lock in this way and releasing
it, then a more subtle blocking lock situation can still occur. If a user goes out to lunch while holding
a sub-exclusive lock on a table, then any shared lock request on the whole table will block at the head
of the request queue, and all other lock requests will queue behind it.

Diagnosing such situations and working out which session to kill is not easy, because the diagnosis
depends on the order of the waiters. Most blocking lock detection utilities do not show the request
order, and do not consider that a waiter can block other sessions even when it is not actually holding
any locks.

Lock Detection Scripts

The following scripts can be used to track and identify blocking locks. The scripts shows the
following lock situation.

Session 1 Session 2
select empno
from emp for update of update emp set ename = 'Müller'
empno; where empno = 7369;
show_dml_locks.sql

This script shows actual DML-Locks (incl. Table-Name), WAIT = YES means
that users are waiting for a lock.

WAI OSUSER PROCESS LOCKER T_OWNER OBJECT_NAME PROGRAM

--- ------- -------- ------- -------- ------------- --------------
NO zahn 8935 SCOTT - Record(s) sqlplus@akira
YES zahn 8944 SCOTT - Record(s) sqlplus@akira
NO zahn 8935 SCOTT SCOTT EMP sqlplus@akira
NO zahn 8944 SCOTT SCOTT EMP sqlplus@akira

show_blocking_sessions.sql

This script show users waiting for a lock, the locker and the SQL-Command they are waiting for a
lock, the osuser, schema and PIDs are shown as well.

Current Lock-Waits

OS_LOCKER LOCKER_SCHEMA LOCKER_PID OS_WAITER WAITER_SCHEMA

WAITER_PID
---------- -------------- ---------- ----------- --------------- ----------
zahn SCOTT 8935 zahn SCOTT 8944

SQL_TEXT_WAITER
--------------------------------------------------------------------------
TX: update emp set ename = 'Müller' where empno = 7369

utllockt.sql

This is the original Oracle script to print out the lock wait-for graph in a tree structured fashion. This
script prints the sessions in the system that are waiting for locks, and the locks that they are waiting
for. The printout is tree structured. If a sessionid is printed immediately below and to the right of
another session, then it is waiting for that session. The session ids printed at the left hand side of the
page are the ones that everyone is waiting for (Session 96 is waiting for session 88 to complete):

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2

----------------- ------------ -------------- ---------- --------- --------
88 None
96 Transaction Exclusive Exclusive 262144 3206

The lock information to the right of the session id describes the lock that the session is waiting for
(not the lock it is holding). Note that this is a script and not a set of view definitions because connect-
by is used in the implementation and therefore a temporary table is created and dropped since you
cannot do a join in a connect-by.
This script has two small disadvantages. One, a table is created when this script is run. To create a
table a number of locks must be acquired. This might cause the session running the script to get
caught in the lock problem it is trying to diagnose. Two, if a session waits on a lock held by more
than one session (share lock) then the wait-for graph is no longer a tree and the conenct-by will show
the session (and any sessions waiting on it) several times.

Distributed Transactions
For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks, because
not all of the lock information is available locally. To prevent distributed transaction deadlocks,
Oracle times out any call in a distributed transaction if it has not received any response within the
number of seconds specified by the _DISTRIBUTED_LOCK_TIMEOUT parameter. This timeout
defaults to 60 seconds. If a distributed transaction times out, an ORA-2049 error is returned to the
controlling session. Robust applications should handle this exception in the same way as local
enqueue deadlocks.

select name,value
from v$parameter
where name = 'distributed_lock_timeout';

NAME VALUE
----------------------------- ------
distributed_lock_timeout 60

ITL Entry Shortages

There is an interested transaction list (ITL) in the variable header of each Oracle data block. When
a new block is formatted for a segment, the initial number of entries in the ITL is set by the
INITRANS parameter for the segment. Free space permitting, the ITL can grow dynamically if
required, up to the limit imposed by the database block size, or the MAXTRANS parameter for the
segment, whichever is less.

Every transaction that modifies a data block must record its transaction identifier and the rollback
segment address for its changes to that block in an ITL entry. (However, for discrete transactions,
there is no rollback segment address for the changes.) Oracle searches the ITL for a reusable or free
entry. If all the entries in the ITL are occupied by uncommitted transactions, then a new entry will be
dynamically created, if possible.

If the block does not have enough internal free space (24 bytes) to dynamically create an additional
ITL entry, then the transaction must wait for a transaction using one of the existing ITL entries to
either commit or roll back. The blocked transaction waits in shared mode on the TX enqueue for one
of the existing transactions, chosen pseudo-randomly. The row wait columns in V$SESSION show the
object, file, and block numbers of the target block. However, the ROW_WAIT_ROW# column
remains unset, indicating that the transaction is not waiting on a row-level lock, but is probably
waiting for a free ITL entry.

The most common cause of ITL entry shortages is a zero PCTFREE setting. Think twice before
setting PCTFREE to zero on a segment that might be subject to multiple concurrent updates to a
single block, even though those updates may not increase the total row length. The degree of
concurrency that a block can support is dependent on the size of its ITL, and failing that, the amount
of internal free space. Do not, however, let this warning scare you into using unnecessarily large
INITRANS or PCTFREE settings. Large PCTFREE settings compromise data density and degrade
table scan performance, and non-default INITRANS settings are seldom warranted.

One case in which a non-default INITRANS setting is warranted is for segments subject to parallel
DML. If a child transaction of a PDML transaction encounters an ITL entry shortage, it will check
whether the other ITL entries in the block are all occupied by its sibling transactions and, if so, the
transaction will roll back with an ORA-12829 error, in order to avoid self-deadlock. The solution in
this case is to be content with a lower degree of parallelism, or to rebuild the segment with a higher
INITRANS setting. A higher INITRANS value is also needed if multiple serializable transactions
may have concurrent interest in any one block.

Check ITL Waits

The following SQL-Statement shows the number of ITL-Waits per table (Interested Transaction List).
INITRANS and/or PCTFREE for those tables is to small (could also be that MAXTRANS is too
small). Note that STATISTICS_LEVEL must be set to TYPICAL or ALL, MAXTRANS has been
desupported in Oracle 10g and now is always 255 (maximum).

select name,value
from v$parameter
where name = 'statistics_level';

NAME VALUE
------------------------------------ -----------
statistics_level TYPICAL

TTITLE "ITL-Waits per table (INITRANS to small)"

set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
--
select owner,
object_name||' '||subobject_name object_name,
value
from v$segment_statistics
where statistic_name = 'ITL waits'
 and value > 0
order by 3,1,2;
--
col owner clear
col object_name clear
col value clear
ttitle off
/

Conclusion
Exclusive Locks lock a resource exclusively, Share Locks can be acquired by more than one Session
as long as the other Session holding a Share Lock have no open Transaction. A Share Lock can be
"switched" from one Session to another.

Application developers can eliminate the risk of deadlocks by ensuring that transactions requiring
multiple resources always lock them in the same order.

A DELETE CASCADE can start out as a simple, single-record delete from a harmless table could
turn into an uncontrollable torrent of cascading deletes and stored database triggers.

Blocking locks are almost always TX (transaction) locks or TM (table) locks. Oracle always performs
locking automatically to ensure data concurrency, data integrity, and statement-level read
consistency. Usually, the default locking mechanisms should not be overriden.

For distributed transactions, Oracle is unable to distinguish blocking locks and deadlocks.

The most common cause of ITL entry shortages is a zero PCTFREE setting.