Locking

ORACLE uses locks to control concurrent access to data. Lock are mechanisms intended to prevent destructive interaction between users accessing ORACLE data. Destructive interaction can be interpreted as any interaction that incorrectly updates data or incorrectly alter underlying data structures. Locks guarantee data integrity while allowing maximum concurrent access to the data by unlinuited users.

There are two types of locking AUTOMATIC and MANUAL.

Automatic locking

ORACLE locking is performed automatically and requires no user action Implicit locking occurs for SQL statement as necessary, depending on the action requested. ORACLE'S lock manager automatically locks table data at row level. By locking table data at row level, contention for the same data is minimized. In general there are two types of locks EXCLUSIVE lock and SHARE lock.

EXCLUSIVE lock prohibits the sharing of the associated resource. The first transaction to exclusively lock a resource is the only transaction that can alter the resource until the exclusive lock is released.

SHARE lock allows the associated resource to be shared, depending on the operations involved (several users can read the same data at the same time). Several transactions can acquire share locks on the same resource. Share locks acquire a higher degree of data concurrency than exclusive locks.

Manual locking

Some times a user may want to override default locking. ORACLE allows manual override of automatic locking which features both the row level and table level locking. A row is locked exclusively so that users can modify the row until the transaction holding the lock is committed or rolled back. A table lock can be in any one of the modes:

Row share
Row exclusive
Share lock
Share row exclusive
Exclusive
It can be obtained in manual locking as

LOCK TABLE table IN ROW SHARE MODE.
LOCK TABLE table IN SHARE EXCLUSIVE MODE.
LOCK TABLE table IN SHARE MODE.
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE.
LOCK TABLE table IN EXCLUSIVE MODE.

Domain Name Search