This is an excerpt from the bestselling book
Oracle Grid & Real Application Clusters. To get immediate
access to the code depot of working RAC scripts, buy it
directly from the publisher and save more than 30%.
Oracle Row-level Locks are ?very
subtle,? in Steve Adam?s words. When a transaction updates or
modifies a row, its transaction identifier is recorded in the entry
as a part of the transaction list. The list is located in the header
of the data block itself, and the row header is modified to point to
the transaction list entry.
Row-Level locks are the locks
that protect selected rows. They are implicit in nature. The
following statements create row-level locks:
* SELECT with the FOR UPDATE
These row locks or row-level
locks are stored in the block, and each lock refers to the global
transaction lock. As in the case of a single instance Oracle, the
RAC controls concurrency down to the row level. The finest lock
granularity is at the row level.
However, to keep the cache
coherent, access to the data blocks is controlled by the GCS. This
has no effect on the row-level lock. GCS resources and row locks
operate independently of the GCS. An instance can request or ship
the data block to another instance in the cluster without affecting
the row-level locks that are held inside the data block. The
row-level lock is fully controlled by the transaction that causes
the row-level lock. When the transaction commits or rolls back, the
row-level lock is released. In the meantime, if another transaction
intends to update the same row, it has to wait until the initial
transaction commits or rolls back.
The row lock method has an
important advantage in maintaining data consistency, even if there
are multiple instances, as in the RAC system. The behavior of the
row lock and the release is the same, whether it is a single
stand-alone database or a multi-instance RAC system. During the row
lock period, even if the data block gets transferred to another
instance, the row lock remains intact until released.
Global Resource Directory (GRD)
The GES and GCS together
maintain a global resource directory (GRD) to record information
about resources and enqueues. The GRD remains in the memory and is
stored on all the instances. Each instance manages a portion of the
directory. The distributed nature of the GRD is a key point for the
fault tolerance of RAC.
The GRD is an internal database
that records and stores the current status of the data blocks.
Whenever a block is transferred out of a local cache to another
instance?s cache, the GRD is updated. The following resource
information is available in GRD:
* Data Block Addresses (DBA).
This is the address of the block being modified.
* Location of most current
version of the data block. This exists only if multiple nodes share
the data block.
* Modes of the data blocks ((N)Null,
(S)Shared, (X)Exclusive ).
* The Roles of the data blocks
(local or global). This indicates the role in which the data block
is being held by the instance.
* SCN ? System Change Number.
* Image of the Data Block ? it
could be past image or current image. Current image represents the
copy of the block held by the current instance. Past image
represents the global dirty data block image maintained in the
More details about the above
resources are covered in later sections.
The GRD is similar to the
previous version of the lock directory from a functional
perspective, but it has been expanded with more components. It
contains an accurate inventory of resource status and location.