An Oracle database contains two types of locks:
- shared and
- exclusive.
The most common type of locks are shared locks that are issued using
SQL SELECT statements, and
exclusive locks that are issued with DELETE and
UPDATE statements.
In shared locking, whenever a unit of data is retrieved from the database, an entry is placed in the database
storage pool.
Exclusive locks are issued for the duration of all 1) UPDATE or 2) DELETE statements to ensure that all modifications are single-threaded within the tables of the database. Oracle must obtain exclusive control of the
segment header block whenever a row is inserted or deleted.
The 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 [1]. Many clients may have shared locks against the same database, but shared locks cannot coexist with exclusive locks.
Whenever an update event occurs, the database attempts to post an exclusive lock against the target row. 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. The exclusive lock will wait if any other tasks hold a shared lock against the target row.
We will begin by looking at Oracle's overall locking scheme.