Locks in Oracle Database 10g

Learning objective

After completing this topic, you should be able to recognize how locks are used to ensure concurrent user access to a database.

1. About locks

Before a session can modify data in a database, it needs to lock the data. Locks prevent multiple sessions from changing the same data at the same time. Data that has been locked by a session cannot be modified by another session until the lock is released.

Locks can be applied to individual rows of data, multiple rows, or entire tables.

Oracle Database 10 g supports manual and automatic locking. Automatic locking always applies to the lowest level possible in a database, to minimize potential conflicts.

For example, if a session is modifying only a single row, only that row will be locked, and not the entire page or table.

In this way, the Oracle locking mechanism preserves maximum data concurrency.

When multiple transactions need to lock the same data, the first transaction to request the lock obtains it. Other transactions must wait until the first transaction is complete.

Transaction 1 

UPDATE hr.employees

SET salary=salary+100

WHERE employee_id=100;


Transaction 2 

UPDATE hr.employees

SET salary=salary*1.1

WHERE employee_id=100;

Transactions that require locks include

• modifications to rows, such as inserts, updates, and deletes
• modifications to tables
• modifications to objects, such as table moves

Data queries do not require locks, and can be executed on locked data. Queries use original values from before the data was locked, which are reconstructed from undo information.

Transactions that modify data obtain locks at row level rather than at page or table level. Modifications to objects, such as table moves, obtain locks at object level rather than locking the whole database or schema.

Question

Identify the characteristics of locks.

Options:

0. They are applied to data that is being queried
0. They can be applied to rows, multiple rows, and tables
0. They can be both manual and automatic
0. They give the session exclusive control over data

Answer

Locks can be applied to rows, multiple rows, and tables. They can be both manual and automatic, and they give the session exclusive control over data.

Option 1 is incorrect. Data queries do not require locks, and can be executed successfully on locked data.

Option 2 is correct. You need locks to perform row-specific modifications such as inserts, updates, and deletes, and table-specific operations such as table moves.

Option 3 is correct. Automatic locking always implements the lowest lock level possible for the object being modified.

Option 4 is correct. No other session can modify the data until the lock applied by the first session is released.

2. Data concurrency

Oracle ensures that each user sees a consistent view of data while enabling many users to access the data at the same time.

By default, the lock mechanism uses a row-level locking mode. Different transactions can thus modify different rows within the same table without interfering with one other.

However, Oracle also supports manual locking at higher levels.

LOCK TABLE hr.employees IN EXCLUSIVE MODE;

There are five different lock modes in Oracle Database 10g.

• EXCLUSIVE
• ROW EXCLUSIVE
• ROW SHARE
• SHARE
• SHARE ROW EXCLUSIVE

EXCLUSIVE

EXCLUSIVE lock mode permits queries on the data in the locked table but no other actions. An exclusive lock is required to drop a table.

ROW EXCLUSIVE

ROW EXCLUSIVE lock mode is automatically obtained when updating, inserting, or deleting data. A single row-exclusive lock is implemented regardless of the number of rows changed. This mode is the same as ROW SHARE, but also prohibits locking in SHARE mode.

ROW SHARE

ROW SHARE lock mode permits multiple sessions to access the locked table concurrently, but prohibits sessions from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

SHARE

SHARE lock mode permits concurrent queries but prohibits updates to the locked table. A SHARE lock is required to create an index on a table.

SHARE ROW EXCLUSIVE

SHARE ROW EXCLUSIVE lock mode allows you to look at the whole table and permits other sessions to query rows in the table, but prohibits them from locking the table in SHARE mode or updating rows.

With manual locking, you can use the NOWAIT argument with the LOCK command to control how sessions wait for locks to be released.

NOWAIT returns control to you immediately if the specified table is already locked by another session.

If you don't specify the argument, the database waits until the table becomes available again, locks it,and returns control to you.

LOCK TABLE hr.employees IN SHARE MODE NOWAIT;


LOCK TABLE hr.employees IN SHARE MODE NOWAIT


*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Question

Match the manual lock modes to their descriptions.

Options:

0. EXCLUSIVE
0. ROW EXCLUSIVE
0. ROW SHARE
0. SHARE
0. SHARE ROW EXCLUSIVE

Targets:

0. Only permits queries on the locked table data
0. Automatically obtained when updating, inserting, or deleting data
0. Permits multiple sessions to access the locked table concurrently but prohibits exclusive locking.
0. Permits concurrent queries but prohibits updates to the locked table
0. Used to query an entire table and permits other sessions to query rows in the table

Answer

The manual lock modes have been matched correctly to their descriptions.

An EXCLUSIVE lock is required to drop a table.

ROW EXCLUSIVE mode is the same as ROW SHARE , but also prohibits locking in SHARE mode.

ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

A SHARE lock is required to create an index on a table.

However, SHARE ROW EXCLUSIVE prohibits sessions from locking the table in SHARE mode or updating rows.

3. DML transaction locking and enqueue

The locking mechanism for DML transactions - inserts, updates, and deletes - requires two locks for each transaction.

• ROW EXCLUSIVE
• SHARE table-level

ROW EXCLUSIVE

A ROW EXCLUSIVE lock is applied to the row or rows being updated. There will be one ROW EXCLUSIVE lock regardless of the number of rows changed.

SHARE table-level

A SHARE table-level lock is applied to the table being updated, to prevent another session from locking the whole table while the change is being made.

Question

Identify the locks required by DML transactions.

Options:

0. EXCLUSIVE
0. ROW EXCLUSIVE
0. SHARE table-level
0. SHARE ROW EXCLUSIVE

Answer

DML transactions require both a ROW EXCLUSIVE lock and a SHARE table-level lock.

Option 1 is incorrect. An EXCLUSIVE lock mode only permits queries on the locked table, and is not required by DML transactions.

Option 2 is correct. A single ROW EXCLUSIVE lock is applied to the row or rows being updated, regardless of their number.

Option 3 is correct. A SHARE table-level lock is applied to the table being updated, to prevent another session from locking the whole table.

Option 4 is incorrect. A SHARE ROW EXCLUSIVE lock mode prohibits other sessions from locking the table in SHARE mode or updating rows. However, this is not used for DML transactions.

Requests for locks are automatically queued by the enqueue mechanism, which tracks

• sessions waiting for locks
• the order in which locks have been requested
• the requested lock mode

In enqueue, as soon as the transaction holding a lock completes, the next session in line receives the lock.

Enqueued sessions that already hold a lock can request to convert that lock without having to go to the end of the queue.

For example, a session holding a shared lock on a table can request to convert the shared lock to an exclusive lock. The session holding the shared lock will then be assigned an exclusive lock without having to rejoin the queue, provided that no other session already has an exclusive or shared lock on the table.

Question

Which of these are tracked by the enqueue mechanism?

Options:

0. Order in which locks were requested
0. Requested lock mode
0. Sessions waiting for locks
0. Type of transaction attempted

Answer

The enqueue mechanism tracks the order in which locks were requested, the requested lock mode, and the sessions waiting for locks.

Option 1 is correct. As soon as the transaction holding a lock completes, the next session in line receives the lock.

Option 2 is correct. Enqueue records the requested lock mode for each request. The requested lock mode can be changed without the session losing its place in the queue.

Option 3 is correct. All sessions that request locks are queued in sequence.

Option 4 is incorrect. The enqueue mechanism only tracks requests for locks, not the transaction that is performed on the locked data.

Summary

Locks prevent multiple sessions from changing the same data at the same time. They can be applied to rows, multiple rows, or entire tables, and can be applied manually or automatically. Automatic locking always applies to the lowest possible level in a database. Data queries do not require locks.

There are five different manual lock modes in Oracle Database 10 g. You can use the NOWAIT argument with a LOCK command to avoid waiting for a lock to be released.

The locking mechanism for DML transactions requires two locks for each transaction: a ROW EXCLUSIVE lock and a SHARE table-level lock. Requests for locks are queued by the enqueue mechanism, which tracks sessions waiting for locks, the order in which locks were requested, and the requested lock mode.