| Lesson 4 | Overview of Oracle lock modes |
| Objective | Describe 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 table1
IN SHARE MODE
has completed.
Share row exclusive table locks (SRX)
Share row exclusive table locks are issued with the
LOCK TABLE table1
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:
- serializable=false and
- 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
