Oracle Database - Table Lock (TM)

Card Puncher Data Processing

About

A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an:

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.

Modes

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

Below the abbreviations means:

  • R = Row
  • S = Shared
  • SS= Sub-shared
  • X = Exclusive

RS

The RS (Row Share), also called a subshare table lock (SS) lock indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

SS

The subshare table lock (SS) is an other name for a Row Share Lock (RS) (see above).

RX

The Row Exclusive Table Lock (RX), also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT … FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.

SX

The subexclusive table lock (SX) is an other name for the Row Exclusive Table Lock (RX)

S

A share table lock (S) held by a transaction allows other transactions to query the table (without using SELECT … FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

SRX

The Share Row Exclusive Table Lock (SRX), also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT … FOR UPDATE) but not to update the table.

SSX

The share-subexclusive table lock (SSX) is an other name for the Share Row Exclusive Table Lock (SRX)

X

The Exclusive Table Lock (X) is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

Full

A full table lock is acquired in this modes:

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - DML Lock

A DML lock, also called a data lock, guarantees the integrity of data accessed concurrently by multiple users. For example, a DML lock prevents two customers from buying the last copy of a book available...
Oracle Database Lock Foreign Key Unindexed
Oracle Database - Locks and Foreign Keys - Concurrency control of parent keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behaviour depends on whether foreign key columns are indexed. If foreign keys are not indexed,...
Oracle Database Row Lock Tx
Oracle Database - Row Locks (TX)

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT ... FOR UPDATE statement. The...



Share this page:
Follow us:
Task Runner