Database Reference
In-Depth Information
In this example, I will create a blocking lock between two sessions connecting to two different instances. Refer to
the following code: session 1 locked the table T1 in exclusive mode. Session 2 tries to lock the table in exclusive mode
while session 1 is holding the lock. Since there is an exclusive lock already held on the table T1, session 2 enqueues
waiting for session 1 to release the lock.
REM From session 1, lock table t1 from instance 1.
SQL> lock table t1 in exclusive mode;
Table locked.
REM From session 2, try to lock table t1 from instance 2.
SQL> lock table t1 in exclusive mode;
<.. Session 2 is waiting for the lock..>
Output of gv$ges_blocking_enqueue is printed in Listing 11-3. Querying gv$ges_blocking_enqueue, you can see
the following details about all blocked locks:
Column
resource_name
uniquely identifies the table T1, as the first part of the string
0xb6dff3 is coded with object_id of the table in hexadecimal format.
1.
Column
STATE
is set to
GRANTED
, implying that lock on the resource has been granted to
PID:17665. Column value OPENING means that the process is waiting for the lock to
be available.
2.
Column
BLOCKED
indicates if the process is blocked or not. A value of 1 in the BLOCKED
column indicates that process is blocked and waiting for lock to be available.
3.
Column
BLOCKER
indicates if the process is blocking another process or not. A value of 1 in
the
BLOCKER
column indicates that the process is blocking another process.
4.
Column
owner_node
indicates the owner node for that lock. PID 3914 is connected to
instance 1 (inst_id=2) and PID 17665 is connected to instance 0 (inst_id=1).
5.
Listing 11-3.
gv$ges_blocking_enqueue
REM At this time, session 2 will be waiting for locks. Let's review global locks.
SQL> col inst_id format 99
SQL> col owner_node format 99 head 'Owner|Node'
SQL> SELECT inst_id, pid, resource_name1, state, owner_node, blocked, blocker
FROM gv$ges_blocking_enqueue
ORDER BY resource_name1;
Owner
INST_ID PID RESOURCE_NAME1 STATE Node BLOCKED BLOCKER
------- ---------- ------------------------------ ---------- ----- ---------- ----------
1 17665 [0xb6dff3][0x0],[TM][ext 0x0,0 GRANTED 0 0 1
2 3914 [0xb6dff3][0x0],[TM][ext 0x0,0 OPENING 1 1 0
View gv$ges_blocking_enqueue can be joined to gv$process to retrieve session-level details. Listing 11-4 prints
a useful script to retrieve session-level details. Session with a SID of 943 is holding a lock in KJUSEREX mode and
session 4312 is waiting for the resource to be available. In this example, the resource type is TM, since we are locking
the table object.
