Discussion of Oracle Lock (locking)

1, the principle of lock (lock)

Locks in Oracle are often business-level issues, and locks are designed to ensure database consistency in concurrency, so there is no lock without concurrency. There are several principles for Oracle locks:

The rows are locked only when modified, because there is an undo relationship and the read does not produce a row lock.

When a statement modifies a record, only the record is locked and no lock escalation exists in the Oracle database.

When a row is modified, it blocks other people from modifying it.

When a transaction modifies a row, a row lock (TX) is added to the row to prevent other transactions from modifying their peers.

Reading will never block writing, but the only exception is select ... for update

Writing will never block reading.

When a row is modified, Oracle provides consistent reading of the data through the rollback segment.

In Oracle, the lock is not a scarce resource, just a property on a block of data.

2, TM Lock and TX lock

TM table locks, which occur during insert,update,delete and select for update operations, are designed to ensure that the operation works properly and prevent other people from performing DDL operations on the table.

Tx Lock Transaction locks (row locks) prevent other sessions from being modified for the data that is being modified.

The TM lock is not the same as the TX lock object, and the TM lock is for the table, the TX lock is for rows, as shown in the following illustration, to modify a row with two locks, one of which is to add a TX lock on the modified line, to prevent other sessions from modifying the row, and another to add a TM lock on the table to prevent the table DDL from being modified.

3. The mode of TM Lock (lock mode)

Row Share (RS)--2

This lock, also called a subshare table lock (SS), indicates which transaction holding the lock on the table has 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.

Row Exclusive Table Lock (RX)---3

This lock, also called a subexclusive table lock (SX), generally indicates that transaction holding the lock has Ed 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.

Share Table Lock (S)--4

A share table lock 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 isn't sufficient to ensure that a transaction can modify the table.

Share Row Exclusive Table Lock (SRX)---5

This lock, also called a share-subexclusive table lock (SSX), are more restrictive than a share table lock. Only one transaction in 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.

Exclusive Table Lock (X)---6

This lock was the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any Type of lock on the

Table.