Oracle Locking Modes

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 query, 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 SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;


1.   How does Oracle handle locking?
====================================

Oracle use enqueues as locking mechanism for managing access to shared
resources. A shared resource can be a table definition, a transaction
or any type of structure that represent something sharable between sessions.
Each type of actions performed by Oracle sessions on those shared resources
will require a certain type of lock or lock mode (e.g. a 'select on a table'
action will require that the executing session has a shared lock on the
resource 'table definition' of the selected table). When conflicting
actions are occuring, Oracle will serialize the processing by putting
a number of sessions in waiting mode until the work of the blocking
session has been completed.

Each enqueue represent a sharable resource. Sessions are acquiring,
converting and releasing locks on resources in function of the work
they need to perform. Releasing locks are performed by the sessions
when they issue a commit or a DDL statement (i.e. implicit commit), or
by SMON if the sessions have been killed. Conversion is the process of
changing a lock from the mode we currently hold to a different mode.
Acquiring a lock is the process of getting a lock on a resource on which we
currently do not have a lock. We are allowed to convert a lock if the mode
we require, is a subset of the mode we hold or is compatible with the
modes already held by other sessions. Otherwise, we wait on the converters
queue of the resource. We are allowed to acquire a lock, if there are no
converters or waiters ahead of us and the mode we require is compatible with
the modes already held by others.  Otherwise, we wait on the waiters queue
of the resource. When a session has a lock on a resource, then it stands
in the owner queue of the resource. When a lock is released or converted,
the converters and waiters are re-checked to see if they can be acquired.
The converters are processed first, then the waiters.

Row locking in Oracle is based on the TX enqueues and is known as
transactional locking. When two or more sessions are changing data on one
row of a table (DML statements on the same record), the first session will
lock the row by putting his transaction reference in the block containing
the row header. The other sessions will look at this lock information
and will wait on the transaction (i.e. the TX enqueue of the blocking session)
of the first session before proceeding. When the first session performs a
commit, the TX resource will be released and the waiters will start their
own locking. The waiting sessions are thus waiting on an exclusive TX
resource, but their TM resources they are holding give the objects they are
in fact waiting on.

If a lock has not been acquired or converted, a deadlock check is made by
the waiting session after a timeout. For example, following situation
generates a deadlock: user A gets an S lock on resource 1, then user B
gets an S lock on resource 2; later, A request an X lock on resource 2
and waits, then B requests an X lock on resource 1 and waits; now, A is
waiting for B to release resource 2, which is waiting for A to release
resource 1; A is indirectly waiting for A. It is a deadlock, generating
a tracefile in the user_dump_dest and and ORA-60 in the detecting session.

The lock and resource information stands within the SGA to allow PMON to
recover in the event of process failure. The PMON is responsible for
releasing the locks of the crashed/killed processes.


2.   How to find the resource definitions?
==========================================

Each resource is represented by an enqueue. An enqueue is identified by
a unique name, also known as the resource name. The name has the form:
<Type, ID1, ID2>. Type has two characters and represent a resource type
(e.g. “TM” for the table definition type). ID1 and ID2 are positive numbers
and identify the resource fully (e.g. ID1 is the object_id  of the table
if the resource type is "TM").

The description of most enqueue/resource types can be found in the appendixes
of the Oracle Reference Guide. The most commonly known resource types are
the TM, TX, ST and UL resources.
a. The TM resource, known as the DML enqueue, is acquired during the execution
of a statement when referencing a table so that the table is not dropped or
altered during the execution of it.
b. The TX resource, known as the transaction enqueue, is acquired exclusive
when a transaction initiates its first change and is held until the transaction
does a COMMIT or ROLLBACK. Row locking is based on TX enqueues. SMON will
acquire it when doing recovery of a killed/crashed process.
c. The ST resource is used to serialize space management tasks when a
session's job requires extents to be allocated to objects.
d. The UL resource represent the user-defined locks defined by the DBMS_LOCK
package.


3.   Which lock modes are required for which table action?
==========================================================

The following table describes what lock modes on DML enqueues are actually
gotten for which table operations in a standard Oracle installation.

 Operation                Lock Mode LMODE Lock Description
------------------------- --------- ----- ----------------
 Select                     NULL     1    null
 Select for update          SS       2    sub share
 Insert                     SX       3    sub exclusive
 Update                     SX       3    sub exclusive
 Delete                     SX       3    sub exclusive
 Lock For Update            SS       2    sub share
 Lock Share                 S        4    share
 Lock Exclusive             X        6    exclusive
 Lock Row Share             SS       2    sub share
 Lock Row Exclusive         SX       3    sub exclusive
 Lock Share Row Exclusive   SSX      5    share/sub exclusive
 Alter table                X        6    exclusive
 Drop table                 X        6    exclusive
 Create Index               S        4    share
 Drop Index                 X        6    exclusive
 Truncate table             X        6    exclusive
-----------------------------------------------------------