Oracle Locks   «Prev  Next»
Lesson 4 Overview of Oracle lock modes
ObjectiveDescribe Oracle lock modes

Oracle Lock Modes

Conncurency

Various modes of Oracle locking provide concurrency management. This is the process of ensuring that all database changes are single-threaded, and that no two tasks can ever update a row at the same moment in time.

Modes of locking

Oracle maintains locks at either the row level or the table level. Unlike other databases such as the IBM DB2 database, Oracle will never "escalate" locks to the table level if the database detects that a majority of the rows in a table is being locked.
Consequently, the Oracle programmer must decide in advance whether to lock the entire table or allow each row of the table to be locked individually.

Types of row locks and table locks

Oracle supports two types of locks: row locks and table locks. These locks can be subdivided into several categories.

Row share table locks (RS) Table share locks (S)
Row exclusive table locks (RX) Exclusive table lock (X)
Share row exclusive table locks (SRX)  

Row share table locks (RS)

Row share table locks are issued when an SQL transaction has declared its intent to update the table in row share mode. This type of lock will allow other queries to update rows in the customer table. For example:

lock table customer in row share mode;
select 
customer_name
from 
customer
for update of customer;

Row exclusive table locks (RX)

Row exclusive locks are issued automatically against a table when an UPDATE, DELETE, or INSERT statement is issued against the table.

Table share locks (S)

A table share lock is issued when the LOCK TABLE command is issued against the table. This indicates that the transaction intends to perform updates against some rows in the table, and prevents any other tasks from execution until the LOCK TABLE xxx IN SHARE MODE has completed.

Share row exclusive table locks (SRX)

Share row exclusive table locks are issued with the LOCK TABLE xxx IN SHARE ROW EXCLUSIVE MODE command. This prevents any other tasks from issuing any explicit LOCK TABLE commands until the task has completed, and prevents any row-level locking on the target table.

Exclusive table locks (X)

An exclusive table lock is the most restrictive of the table locks and prevents everything except queries against the affected table. Exclusive locks are used when the programmer desires exclusive control over a set of rows until their operation has completed. The following command is used to lock the CUSTOMER table for the duration of the task:

LOCK TABLE CUSTOMER IN ROW EXCLUSIVE MODE NOWAIT;

As you will see in a later lesson, the Oracle V$ tables can be queried to gather information about each of these types of locks.

Locking Parameters

Two init.ora parameters control locking:
  1. serializable=false and
  2. row_locking=always.

These default values should not be changed except in very rare cases. In the next lesson, we'll take a look at database deadlocks.

Oracle Lock Modes - Exericse

Before going on, click the Exercise link below to complete an exercise on the different lock modes.
Oracle Lock Modes - Exericse