Oracle Locks   «Prev  Next»
Lesson 3Types of locks and their functions
ObjectiveDescribe the purpose of shared and exclusive Oracle locks.

Shared versus Exclusive Oracle locks

Describe the purpose of shared and exclusive locks when using the Oracle relational database management system.

The Role of Shared and Exclusive Locks in Oracle RDBMS

In Oracle's Relational Database Management System (RDBMS), data integrity and concurrency control are of paramount importance. To ensure that multiple transactions can occur simultaneously without compromising the integrity of the data, Oracle employs a sophisticated locking mechanism. Two fundamental types of locks crucial to this mechanism are Shared Locks and Exclusive Locks. This document elucidates the purpose, functionality, and implications of these locks within Oracle RDBMS.

Shared Locks

  1. Purpose: The primary purpose of Shared Locks is to facilitate concurrent read operations while preventing any concurrent write operations on the same data.
  2. Functionality: When a Shared Lock is applied to a database row or object, multiple transactions can read the data but are precluded from making any modifications to it. This ensures that the data remains consistent throughout the duration of the transactions.
  3. Implications: Shared Locks allow for high data availability since they do not restrict read operations. However, they can lead to potential bottlenecks if many transactions are waiting to update the same data, as they must wait for the Shared Lock to be released.

Exclusive Locks

  1. Purpose: Exclusive Locks serve to provide a transaction with exclusive access to a database row or object for both reading and writing.
  2. Functionality: When an Exclusive Lock is enforced, it prohibits other transactions from either reading or modifying the locked data until the lock is released. This ensures complete isolation of the transaction, thus maintaining data integrity.
  3. Implications: While Exclusive Locks offer the highest level of data integrity, they can be detrimental to data availability. When an Exclusive Lock is applied, it essentially serializes access to the particular data, which may result in performance bottlenecks in systems with high levels of concurrent transactions.
  4. Strategic Utilization: Choosing between Shared and Exclusive Locks is a matter of balancing data integrity against system performance. Shared Locks are more conducive to read-heavy environments where data consistency during read operations is critical. Conversely, Exclusive Locks are ideal for scenarios where write operations are prevalent, and strict isolation is required to maintain data integrity.
Shared and Exclusive Locks are instrumental in Oracle RDBMS for achieving specific goals: Shared Locks aim for higher data availability and read consistency, while Exclusive Locks prioritize data integrity through strict isolation. Understanding the purpose and implications of these locking mechanisms is essential for database administrators and developers alike to optimize database performance, maintain data integrity, and ensure successful transactional outcomes.
In any Oracle database, you will see two types of locks: shared and exclusive. The most common types of locks are
  1. Shared locks that are issued with SQL SELECT statements
  2. Exclusive locks that are issued with DELETE and UPDATE statements

Shared locks

In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database storage pool.

Exclusive Locks

Exclusive locks are issued for the duration of all SQL UPDATE or DELETE statements to ensure that all changes are single-threaded through the database. As you may know, Oracle must obtain exclusive control of the segment header block (the first block in a table) whenever a row is inserted or deleted. To understand the difference between shared and exclusive locks, assume that a task wants to lock a row for update. The task will need to wait until all shared locks are released for this row before it can issue the exclusive lock. In short, the exclusive lock will wait if any other tasks hold a shared lock against the target row.

Coexistence Method

The usual size of a lock is managed internally by Oracle, and the lock will be held by the database until a COMMIT, END, or ABORT message releases the lock. Oracle's locking schemes use a coexistence method. For example, many clients may have shared locks against the same resource, but shared locks cannot coexist with exclusive locks.
Note: Whenever an update event occurs, the database attempts to post an exclusive lock against the target row.

Automatic Locking

Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested. Oracle’s sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized. Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained on a single resource. Both exclusive and share locks always allow queries on the locked resource, but prohibit other activity on the resource (such as updates and deletes).

Manual Locking

Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level.
Now let us take a look two other modes of locking: table and row locks.