Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E10592-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Automatic Locks in DML Operations

The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users. For example, a DML lock can prevent multiple customers from buying the last copy of a book available from an online bookseller. DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.

DML statements automatically acquire locks at both the table level and the row level. In the sections that follow, the acronym in parentheses after each type of lock or lock mode is the abbreviation used in the Locks Monitor of Oracle Enterprise Manager. Enterprise Manager might display "TM" for any table lock, rather than indicate the mode of table lock (such as RS or SRX).

The types of row and table locks are summarized here. For a more complete discussion of the types of row and table locks, see Oracle Database Concepts.

Row Locks (TX) A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock for the table in which the row resides. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM) A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. You can explicitly obtain a table lock using the LOCK TABLE statement, as described in "Manual Data Locking".

A table lock can be held in any of the following modes:

Locks in DML Operations

Oracle Database automatically obtains row-level and table-level locks on behalf of DML operations. The type of operation determines the locking behavior. Table B-1 summarizes the information in this section.

Note:

The implicit SX locks shown for the DML statements in Table B-1 can sometimes be exclusive (X) locks for a short time owing to side effects from constraints.

Table B-1 Summary of Locks Obtained by DML Statements

SQL Statement Row Locks Table Lock Mode RS RX S SRX X

SELECT ... FROM table...

none

Y

Y

Y

Y

Y

INSERT INTO table ...

Yes

SX

Y

Y

N

N

N

UPDATE table ...

Yes

SX

Y*

Y*

N

N

N

MERGE INTO table ...

Yes

SX

Y

Y

N

N

N

DELETE FROM table ...

Yes

SX

Y*

Y*

N

N

N

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

Yes

SX

Y*

Y*

N

N

N

LOCK TABLE table IN ...

           

   ROW SHARE MODE

 

SS

Y

Y

Y

Y

N

   ROW EXCLUSIVE MODE

 

SX

Y

Y

N

N

N

   SHARE MODE

 

S

Y

N

Y

N

N

   SHARE ROW EXCLUSIVE MODE

 

SSX

Y

N

N

N

N

   EXCLUSIVE MODE

 

X

N

N

N

N

N

* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.


Locks When Rows Are Queried

A query can be explicit, as in the SELECT statement, or implicit, as in most INSERT, MERGE, UPDATE, and DELETE statements. The only DML statement that does not necessarily include a query component is an INSERT statement with a VALUES clause. Because queries only read data, they are the SQL statements least likely to interfere with other SQL statements.

The following characteristics apply to a query without the FOR UPDATE clause:

Locks When Rows Are Modified

Some databases use a lock manager to maintain a list of locks in memory. Oracle Database, in contrast, stores lock information in the data block that contains the locked row. Each row lock affects only a single row.

Oracle Database uses a queuing mechanism for acquisition of row locks. If a transaction requires a row lock, and if the row is not already locked, then the transaction acquires a lock in the row's data block. The transaction itself has an entry in the interested transaction list (ITL) section of the block header. Each row modified by this transaction points to a copy of the transaction ID stored in the ITL. Thus, 100 rows in the same block modified by a single transaction require 100 row locks, but all 100 rows reference a single transaction ID.

When a transaction ends, the transaction ID remains in the ITL section of the data block header. If a new transaction wants to modify a row, then it uses the transaction ID to determine whether the lock is active. If the lock is active, then the session of the new transaction asks to be notified when the lock is released; otherwise, the new transaction acquires the lock.

The characteristics of INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE statements are as follows:

See Also:

Oracle Database Concepts for information on locks in foreign keys