Database Reference
In-Depth Information
■
I am using a procedure named print_table to pretty print the columns. print_table is a pL/sQL procedure
written by tom kyte. In a nutshell, that procedure prints the column values in a row format. We will be using the
print_table procedure in our topic to improve readability of the code output. search on your favorite search engine
for “print_table tom kyte” or it can also be downloaded from the following UrL:
Note
Lock Modes
Resources can be locked in various modes in RAC too. In single instance, six locking modes (1 through 6) are available to
lock a resource. Similarly, RAC too has six locking modes. In fact, there is a one-to-one mapping between a single-instance
locking mode and RAC locking mode, as shown in Table
11-1
. Semantics of these single-instance locking modes are
functionally equivalent to locking modes in RAC. Table
11-1
shows locking modes and their meaning.
Table 11-1.
Lock Modes
Single
Instance
Single-Instance
Comment
RAC
RAC Comment
1
NULL
KJUSERNL
Null mode. Placeholder lock, acquired so that process can
be notified if there is any change in resource state.
2
Row Share
KJUSERCR
Row Share—Concurrent read.
3
Row Exclusive
KJUSERCW
Row Exclusive—Concurrent write.
4
Share
KJUSERPR
Protected read, write allowed if only one holds share mode.
5
Share Row Exclusive
KJUSERPW
One process can modify, others can read.
6
Exclusive
KJUSEREX
One process holds exclusive lock. Others can't access.
It is important to understand most common locking modes for TX/TM resources. I will discuss the meaning of
TX/TM lock modes later in this chapter.
Lock-Related Views
Dynamic view gv$lock shows all
single
-instance locks
globally
. View gv$lock is based upon the fixed table x$ksqst.
Accessing gv$lock view retrieves data from x$ksqst table from all instances, processes rows, and returns the result set.
Rows from the gv$lock and gv$resource views do not retrieve any information from GRD.
In contrast, Dynamic view gv$ges_resource shows global resource and it is based upon two fixed tables: X$KJBR
and X$KJIRFT. Fixed table X$KJBR externalizes resources structures of BL resource type and fixed table X$KJIRFT
externalizes resource structures of non-BL resource types such as TX, TM, etc. Similarly, gv$ges_enqueue is based
upon the fixed tables X$KJBL and X$KJILKFT. Fixed table X$KJBL externalizes BL locks and X$KJILKFT externalizes
non-BL locks. These four fixed tables are at the heart of the global locking scheme in RAC.
