Database Reference
In-Depth Information
Enqueue Contention
In this section, I will discuss locking contention, which is frequently seen in a typical RAC database.
TX Enqueue Contention
Oracle database provide row-level locking abilities for a fine-grained locking granularity. Sessions trying to modify a
row locked by another transaction must wait for the transaction to commit or rollback before modifying the row.
In a single-instance database, row-level locking scheme is implemented using TX resources and locks;
a
TX resource
protects
one
transaction. A session initiating a transaction will hold exclusive mode lock on a TX resource, and a
session waiting to modify the row locked by the first transaction will request exclusive mode lock on the lock holder's
TX resource. After the lock holder completes the transaction, that is, commit or rollback, holding session will release
the lock on TX resource. As the lock request is complete, lock waiter will continue to modify the row.
In a RAC database, sessions connected to two different instances could try to modify
a
row. So, TX resources and
locks are globalized in a RAC database. A global locking scheme is employed to protect the TX resources globally.
I will explain TX resource contention in a RAC database using a simple example: In the following code, session #1
updated a row, thereby initiating a new transaction. Transaction_id of the transaction is
1557.11.9239
as retrieved
using
dbms_transaction
packaged call. From session #2, I will try to update the same row and since the row is locked
by session #1, session #2 will wait for the lock to be available. Essentially, session #2 will wait for the transaction
initiated by session #1 to complete.
REM From session 1, I will lock a row. SID=6445
SQL> update t1 set n1=n1 where n1=100;
select dbms_Transaction.LOCAL_TRANSACTION_ID from dual;
LOCAL_TRANSACTION_ID
--------------------
1557.11.9239
REM From session 2 connected to a different instance, I will update the same row.
REM SID=12229
SQL> update t1 set n1=n1 where n1=100;
In single-instance views, TX resources are visible in v$lock family of views. In the following output, a TX resource
with id1=102039563 and id2=9239 is held in exclusive mode by session 6445. Session with SID=12229 is requesting a
lock on the same TX resource in exclusive mode. The combination of enqueue types id1 and id2 uniquely identifies a
transaction.
SELECT sid, type, id1, id2, lmode, request
FROM gv$lock WHERE (type, id1, id2) IN
(SELECT type, id1, id2 FROM gv$lock WHERE request>0) ;
SID TY ID1 ID2 LMODE REQUEST
------ -- ---------- ---------- ----- -------
6445 TX 102039563 9239 6 0
12229 TX 102039563 9239 0 6
Now, let's review the global locks. In Listing 11-5, output of ges_blocking_locks.sql script shows that session
6445 is holding a lock on a global TX resource in KJUSEREX mode, and session 12229 is waiting for the lock and
has requested the lock in KJUSEREX mode. State
OPENING
indicates that the session is waiting for the resource to be
available. Wait event confirms the row-level lock wait.
