Lesson 3 | Types of locks and their functions |
Objective | Describe the purpose of shared and exclusive Oracle locks. |
Shared versus Exclusive Oracle locks
In any Oracle database, you will see two types of locks:
shared and
exclusive.
The most common types of locks are
- Shared locks that are issued with SQL SELECT statements
- 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.