Database Reference
In-Depth Information
Chapter 11
Locks and Deadlocks
by Riyaj Shamsudeen
Oracle database uses a locking scheme as a coordination mechanism to protect critical resources from concurrent
changes. In a single-instance database, instance-level locks are sufficient as the locks need to be visible in the local
instance only, so local locks are sufficient. In a RAC database, resources must be globally visible and so a new
layer—Global Enqueue Services (GES)—is employed to implement a global locking scheme.
Global Resource Directory (GRD) is a memory area designed to keep track of the state of resources in a RAC
database. GRD is distributed across all active instances of a database. This distribution removes a single point of
failure and resource contention.
Resources and Locks
In this context, a resource is a structure designed to protect an entity from concurrent changes and act as a coordination
mechanism for concurrency control. Locks are acquired on that resource before altering the entity. For example,
a database table is a lockable entity, and before altering a table, a
resource
is allocated uniquely representing that table.
Then, a
lock
is acquired on that resource in a specific mode. Another session modifying the same table must acquire
a lock on the resource before altering the table. If the requested lock mode is incompatible with mode already held,
then the requesting session must wait for the locks to be available. With this locking scheme, concurrent changes are
coordinated to avoid consistency issues.
In a single-instance database, entities are modified only by a single instance, so single-instance resources and locks
are sufficient to implement a locking scheme. In a RAC database, an entity can be modified by any instance of a database
and hence changes must be globally coordinated. Globally maintained structures are required, as local resources
and locks are not sufficient. For example, a session in instance 1 locks a row and another session in instance 2 tries to
modify the same row concurrently while the transaction initiated by the session 1 is still active. A global locking scheme,
GES layer, is employed to protect critical resources from concurrent changes globally. As in a single-instance locking
scheme, every global resource has an associated resource structure: locking a global resource means that a global locking
structure is associated with a global resource.
Figure
11-1
shows the structures employed in this global locking scheme. In this figure, a database table is to be
locked. A resource uniquely representing that table is allocated to protect it. The resource uniquely represents the
table by coding object_id of the table in the resource_name. Further, a resource structure has two queues: converting
queue and granted queue. If a process succeeds in acquiring a lock on a resource, then that process is added to the
granted queue of the resource. If the resource is not available or if the requested lock mode is incompatible with
lock mode held already, then the requesting process is added to the converting queue. In Figure
11-1
, one process is
holding a lock on the resource, and two processes are waiting for the resource to be available.
