Oracle RAC 19c Masterclass — Part 17

By | | ,

Global Enqueue Service (GES), Blocking, Locks & Deadlocks

In Part 16, we used AWR and ASH to identify where RAC performance problems come from.

Now we go one level deeper.

This part is about one of the most important RAC troubleshooting skills:

Finding why one session is blocking another session — especially when the sessions are running on different RAC instances.

We’ll move from theory to production-style troubleshooting.

1. GCS vs GES — Don’t Mix Them Up

Oracle RAC has two major global coordination mechanisms.

                    Oracle RAC
                       |
             +---------+---------+
             |                   |
            GCS                 GES
             |                   |
      Global Cache Service   Global Enqueue Service
             |                   |
       Data blocks          Locks / Enqueues
             |                   |
        Cache Fusion       Global locking
GCS

GCS manages access to cached database blocks.

Typical waits:

gc cr request
gc current request
gc current block busy
gc buffer busy
GES

GES manages global enqueues and locks.

Examples include:

TX
TM
UL

So:

GCS → blocks

GES → locks/enqueues

This distinction is fundamental for RAC troubleshooting.

2. What Is an Enqueue?

An enqueue is a locking mechanism used by Oracle to coordinate access to resources.

For example:

TX → Transaction enqueue
TM → DML enqueue

A simplified example:

Session A
   |
   | UPDATE
   v
Row X
   |
   | locked
   v
Session B
   |
   | UPDATE same row
   v
WAIT

The second session must wait until the first transaction releases the resource.

3. Local vs Global Locking

In a single-instance database:

Instance
   |
   +-- Session A
   |
   +-- Session B
   |
   +-- Lock Manager

In RAC:

             RAC
        +-----------+
        |           |
      ORCL1       ORCL2
        |           |
    Session A   Session B
        |           |
        +-----+-----+
              |
        Global coordination
              |
             GES

This is why RAC locking can be more complex.

4. A Simple RAC Blocking Scenario

Imagine:

ORCL1
Session 101
      |
      | UPDATE CUSTOMER
      |
      v
Customer ID = 100
      ^
      |
      | UPDATE CUSTOMER
      |
Session 205
ORCL2

Session 101 modifies the row but does not commit.

Session 205 attempts to modify the same row.

Result:

ORCL2 Session 205
        |
        ↓
     WAITING
        |
        ↓
ORCL1 Session 101
        |
        ↓
     BLOCKER

This is a cross-instance blocking situation.

5. First Tool: GV$SESSION

When investigating blocking, start with:

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       event,
       sql_id,
       blocking_instance,
       blocking_session,
       seconds_in_wait
FROM gv$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

This can immediately reveal:

INST_ID SID  BLOCKING_INSTANCE BLOCKING_SESSION
------- ---- ----------------- -----------------
2       205  1                 101

Meaning:

ORCL2 / SID 205
       ↓
blocked by
       ↓
ORCL1 / SID 101

6. Find the Blocker

Now query the blocking session:

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       machine,
       program,
       module,
       service_name,
       sql_id,
       event,
       logon_time
FROM gv$session
WHERE sid = 101
AND inst_id = 1;

Don’t stop at the SID.

You want to understand:

  • Who owns the session?
  • Which application?
  • Which service?
  • Which machine?
  • Which SQL?
  • How long has it been connected?

7. The Most Important Question

Finding the blocker is only step one.

You need to ask:

Why hasn’t the blocker committed or rolled back?

For example:

Session 101
   |
   +-- transaction started 45 minutes ago
   |
   +-- application waiting
   |
   +-- client disconnected
   |
   +-- transaction still open

This is a classic production problem.

8. Find Transaction Information

You can correlate sessions with transactions:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       t.start_date,
       t.start_time
FROM gv$session s
JOIN gv$transaction t
  ON s.taddr = t.addr
 AND s.inst_id = t.inst_id
ORDER BY t.start_date;

Depending on your Oracle release/view definitions, transaction timing columns can differ, so verify the available columns in your environment.

9. A Better Transaction View

Useful transaction information includes:

SELECT inst_id,
       addr,
       xidusn,
       xidslot,
       xidsqn,
       start_time,
       used_ublk,
       used_urec
FROM gv$transaction
ORDER BY start_time;

This can tell you:

  • Transaction age
  • Undo usage
  • Transaction identity

A transaction running for a very long time deserves investigation.

10. Why Long Transactions Are Dangerous

Suppose:

09:00
Session A starts transaction

09:01
UPDATE customer

09:05
Another session needs same row

09:10
Still waiting

09:30
Still waiting

The problem isn’t necessarily RAC.

The problem may simply be:

An application transaction remained open for 30 minutes.

RAC only makes the coordination between instances more complex.

11. GV$LOCK

Another important view is:

SELECT inst_id,
       sid,
       type,
       id1,
       id2,
       lmode,
       request,
       block
FROM gv$lock
ORDER BY inst_id, sid;

Important columns:

LMODE

Current lock mode.

REQUEST

Requested lock mode.

BLOCK

Whether the session is blocking another session.

12. Understanding LMODE

Common lock modes include:

0 = None
1 = Null
2 = Row-S
3 = Row-X
4 = Share
5 = S/Row-X
6 = Exclusive

For example:

LMODE = 6

means:

Exclusive lock.

13. Find Current Blockers

A quick query:

SELECT inst_id,
       sid,
       type,
       id1,
       id2,
       lmode,
       request,
       block
FROM gv$lock
WHERE block = 1
ORDER BY inst_id, sid;

This gives you sessions currently identified as blockers at the lock level.

14. Find Sessions Waiting for Locks

SELECT inst_id,
       sid,
       type,
       id1,
       id2,
       lmode,
       request,
       block
FROM gv$lock
WHERE request > 0
ORDER BY inst_id, sid;

A session with:

REQUEST > 0

is requesting a lock mode it doesn’t currently have.

15. Join Blocker and Waiter

A more useful diagnostic query:

SELECT
    w.inst_id      AS waiter_inst,
    w.sid          AS waiter_sid,
    b.inst_id      AS blocker_inst,
    b.sid          AS blocker_sid,
    w.type,
    w.id1,
    w.id2,
    w.request      AS waiter_request,
    b.lmode        AS blocker_mode
FROM gv$lock w
JOIN gv$lock b
  ON w.type = b.type
 AND w.id1  = b.id1
 AND w.id2  = b.id2
WHERE w.request > 0
AND b.block = 1;

This starts building the blocking relationship:

Waiter
   ↓
Resource
   ↓
Blocker

16. Add Session Information

Now make the report more useful:

SELECT
    ws.inst_id AS waiter_inst,
    ws.sid     AS waiter_sid,
    ws.username AS waiter_user,
    ws.sql_id  AS waiter_sql,
    bs.inst_id AS blocker_inst,
    bs.sid     AS blocker_sid,
    bs.username AS blocker_user,
    bs.sql_id  AS blocker_sql,
    wl.type
FROM gv$lock wl
JOIN gv$lock bl
  ON wl.type = bl.type
 AND wl.id1  = bl.id1
 AND wl.id2  = bl.id2
JOIN gv$session ws
  ON ws.inst_id = wl.inst_id
 AND ws.sid = wl.sid
JOIN gv$session bs
  ON bs.inst_id = bl.inst_id
 AND bs.sid = bl.sid
WHERE wl.request > 0
AND bl.block = 1;

Now you can see:

Waiter:
ORCL2 / SID 205
SQL = ABC123

Blocker:
ORCL1 / SID 101
SQL = XYZ789

17. TX Locks

One of the most common locks you’ll encounter during application contention is:

TX

TX is associated with transactions.

For example:

UPDATE same row

can produce TX contention.

Typical wait:

enq: TX - row lock contention

18. Recognizing TX Contention

Check:

SELECT inst_id,
       sid,
       serial#,
       username,
       event,
       sql_id,
       blocking_instance,
       blocking_session,
       seconds_in_wait
FROM gv$session
WHERE event = 'enq: TX - row lock contention'
ORDER BY seconds_in_wait DESC;

This is one of the most useful queries during an application incident.

19. TM Locks

Another important enqueue is:

TM

TM is related to table-level DML locking.

A common cause of TM contention can involve:

  • DML
  • Foreign keys
  • Missing indexes on foreign keys
  • Concurrent parent/child operations

This is particularly important in OLTP systems.

20. The Classic Foreign-Key Problem

Imagine:

PARENT
-------
ID

CHILD
-----
PARENT_ID

If the child table doesn’t have a suitable index on:

CHILD.PARENT_ID

certain concurrent parent operations can result in serious locking problems.

Example:

CREATE INDEX child_parent_fk_i
ON child(parent_id);

The exact indexing strategy depends on workload and schema design, but the principle is important:

Foreign-key indexing can be critical for concurrency.

21. Why This Becomes Worse in RAC

In a single instance:

Session A
Session B
   ↓
Local lock manager

In RAC:

ORCL1 Session A
       |
       |
      GES
       |
       |
ORCL2 Session B

The coordination is now cluster-wide.

Therefore cross-instance contention can add overhead.

22. Enqueue Waits

Check:

SELECT inst_id,
       event,
       COUNT(*) sessions
FROM gv$session
WHERE status = 'ACTIVE'
AND event LIKE 'enq:%'
GROUP BY inst_id, event
ORDER BY sessions DESC;

Example:

ORCL1 → enq: TX - row lock contention → 3
ORCL2 → enq: TX - row lock contention → 25

That’s an immediate signal that ORCL2 deserves attention.

23. Blocking Chains

One blocker can block many sessions.

Example:

                 ORCL1
               Session 101
                    |
                    ↓
                 RESOURCE
          +---------+---------+
          |         |         |
          ↓         ↓         ↓
       ORCL2      ORCL2      ORCL2
       SID 201    SID 202    SID 203

This is far more serious than a single blocked session.

The blocker becomes a root blocker.

24. Find the Root Blocker

A useful approach is to repeatedly follow:

blocked session
       ↓
blocking session
       ↓
blocking session
       ↓
root blocker

Oracle can expose blocking information through GV$SESSION.

For more complex chains, build a recursive diagnostic query or use ASH to reconstruct the chain historically.

25. Example Blocking Chain

Suppose:

ORCL2 SID 300
      ↓
blocked by
      ↓
ORCL1 SID 200
      ↓
blocked by
      ↓
ORCL2 SID 100

The real problem is:

ORCL2 SID 100

because killing SID 200 may only move the problem somewhere else.

This is why:

Always identify the root blocker before taking action.

26. Blocking Tree

Think about it as:

ROOT BLOCKER
     |
     +---- Session A
     |
     +---- Session B
     |
     +---- Session C
              |
              +---- Session D

The root blocker can have a huge impact.

27. ASH for Historical Blocking

If the incident happened yesterday, current GV$SESSION won’t help.

Use:

SELECT sample_time,
       instance_number,
       session_id,
       session_serial#,
       blocking_inst_id,
       blocking_session,
       blocking_session_serial#,
       sql_id,
       event
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
      TIMESTAMP '2026-08-07 09:00:00'
  AND TIMESTAMP '2026-08-07 09:30:00'
AND blocking_session IS NOT NULL
ORDER BY sample_time;

This is extremely powerful.

28. Historical Blocking by Instance

SELECT instance_number,
       blocking_inst_id,
       blocking_session,
       COUNT(*) samples
FROM dba_hist_active_sess_history
WHERE sample_time BETWEEN
      TIMESTAMP '2026-08-07 09:00:00'
  AND TIMESTAMP '2026-08-07 09:30:00'
AND blocking_session IS NOT NULL
GROUP BY instance_number,
         blocking_inst_id,
         blocking_session
ORDER BY samples DESC;

Now you can determine which instance contained the blocker.

29. Find the SQL of the Blocker

Once you know the blocking SID, query ASH:

SELECT sample_time,
       instance_number,
       session_id,
       sql_id,
       event,
       wait_class,
       module,
       service_name
FROM dba_hist_active_sess_history
WHERE instance_number = :inst_id
AND session_id = :sid
AND sample_time BETWEEN :start_time AND :end_time
ORDER BY sample_time;

Now you can see what the blocker was actually doing.

30. The Blocker May Not Be “Doing” Anything

This is a common trap.

You find:

Blocker SID 101
STATUS = INACTIVE

You might think:

“It’s inactive, so it can’t be the problem.”

Wrong.

A session can be:

INACTIVE

while still holding an uncommitted transaction.

For example:

Application:
UPDATE
      ↓
Transaction open
      ↓
Connection becomes idle
      ↓
No COMMIT

The session appears inactive but continues holding locks.

31. Find Idle Sessions Holding Transactions

A useful investigation:

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et,
       t.start_time,
       t.used_ublk
FROM gv$session s
JOIN gv$transaction t
  ON s.taddr = t.addr
 AND s.inst_id = t.inst_id
WHERE s.status = 'INACTIVE'
ORDER BY t.start_time;

This can identify suspicious idle transactions.

32. Application Connection Pools

This is common in modern applications.

Example:

Application
    |
Connection Pool
    |
    +-- Connection 1
    +-- Connection 2
    +-- Connection 3

A connection may remain open while a transaction remains active.

Therefore:

Connection pool configuration and transaction handling are part of Oracle performance troubleshooting.

Not everything is a database problem.

33. Deadlocks

Now we reach a more serious situation.

A deadlock occurs when:

Session A waits for Session B

Session B waits for Session A

Diagram:

Session A
   |
   | waits for
   v
Resource B
   ^
   | held by
   |
Session B
   |
   | waits for
   v
Resource A

There is no way forward.

Oracle detects the cycle.

34. ORA-00060

A typical error is:

ORA-00060: deadlock detected while waiting for resource

In RAC, the situation can span instances.

Example:

ORCL1 Session A
      |
      ↓
Resource X
      ↑
      |
ORCL2 Session B
      |
      ↓
Resource Y
      ↑
      |
ORCL1 Session A

Oracle must detect the global dependency cycle.

35. Deadlock ≠ Blocking

This distinction is critical.

Blocking
A → waits for B

Eventually B commits.

Deadlock
A → waits for B
B → waits for A

Neither can proceed.

So:

Blocking can be normal. Deadlocks are always a problem to investigate.

36. Finding Deadlock Information

When Oracle detects a deadlock, it generates diagnostic information.

Check the alert log and trace files.

For example:

adrci

Then:

show alert

or inspect the relevant diagnostic trace.

The trace can contain a deadlock graph.

37. Deadlock Graph

A simplified graph might look like:

---------Blocker(s)--------
Resource TX-12345
Session 100
Instance 1

---------Waiter(s)---------
Resource TX-67890
Session 200
Instance 2

The full trace provides the relationships needed to identify the application transactions involved.

38. Common Deadlock Cause

A classic application pattern:

Transaction A
UPDATE CUSTOMER
UPDATE ORDER
COMMIT
Transaction B
UPDATE ORDER
UPDATE CUSTOMER
COMMIT

If they run concurrently:

A locks CUSTOMER
B locks ORDER

A waits for ORDER
B waits for CUSTOMER

Deadlock.

39. The Solution Is Usually Application Design

The solution isn’t:

Increase SGA
Increase PGA
Increase processes

Instead:

Make transactions acquire resources in a consistent order.

For example:

Always:

CUSTOMER
   ↓
ORDER

instead of:

Transaction A:
CUSTOMER → ORDER

Transaction B:
ORDER → CUSTOMER

Consistent locking order dramatically reduces deadlock risk.

40. RAC Makes Deadlocks More Interesting

A cross-instance deadlock may look like:

ORCL1
Session 100
   |
   | holds TX A
   ↓
 waits for TX B
   ↑
   |
ORCL2
Session 200
   |
   | holds TX B
   ↓
 waits for TX A

GES coordinates the global resource relationships.

This is one reason RAC DBAs need to understand both:

GCS

and:

GES

41. GES Monitoring Views

Depending on your Oracle 19c environment, useful RAC diagnostic views include:

GV$GES_RESOURCE
GV$GES_BLOCKING_ENQUEUE
GV$GES_ENQUEUE
GV$GES_STATISTICS

You should always verify exact columns in your installation:

DESC GV$GES_RESOURCE;
DESC GV$GES_BLOCKING_ENQUEUE;
DESC GV$GES_ENQUEUE;

Don’t build production scripts based on assumed column names.

42. Inspect GES Resources

For example:

SELECT *
FROM gv$ges_resource;

For a targeted investigation, filter the view based on the columns available in your environment.

The objective is to understand:

Resource
Owner
Requesters
Instance
Lock state

43. GES Statistics

You can inspect global enqueue activity with:

SELECT inst_id,
       *
FROM gv$ges_statistics;

Because the exact output can be extensive, use:

DESC GV$GES_STATISTICS;

first, then select only the relevant counters.

This is a good habit for expert-level Oracle administration:

Discover the view structure instead of assuming it.

44. A Practical RAC Lock Diagnostic Script

Here is a useful starting point:

SET LINESIZE 250
SET PAGESIZE 100

COLUMN waiter FORMAT A15
COLUMN blocker FORMAT A15
COLUMN event FORMAT A35
COLUMN sql_id FORMAT A15

SELECT
    ws.inst_id || ':' || ws.sid AS waiter,
    bs.inst_id || ':' || bs.sid AS blocker,
    ws.username AS waiter_user,
    bs.username AS blocker_user,
    ws.event,
    ws.sql_id AS waiter_sql,
    bs.sql_id AS blocker_sql,
    ws.seconds_in_wait
FROM gv$session ws
LEFT JOIN gv$session bs
       ON bs.inst_id = ws.blocking_instance
      AND bs.sid = ws.blocking_session
WHERE ws.blocking_session IS NOT NULL
ORDER BY ws.seconds_in_wait DESC;

This is something you can keep in your RAC DBA toolkit.

45. A More Complete Blocking Report

Add application information:

SELECT
    ws.inst_id || ':' || ws.sid AS waiter,
    bs.inst_id || ':' || bs.sid AS blocker,

    ws.username AS waiter_user,
    bs.username AS blocker_user,

    ws.service_name AS waiter_service,
    bs.service_name AS blocker_service,

    ws.module AS waiter_module,
    bs.module AS blocker_module,

    ws.sql_id AS waiter_sql,
    bs.sql_id AS blocker_sql,

    ws.event,
    ws.seconds_in_wait
FROM gv$session ws
LEFT JOIN gv$session bs
       ON bs.inst_id = ws.blocking_instance
      AND bs.sid = ws.blocking_session
WHERE ws.blocking_session IS NOT NULL
ORDER BY ws.seconds_in_wait DESC;

This can quickly tell you:

Application A
     ↓
blocked by
     ↓
Batch Application

which is much more actionable.

46. Before Killing a Session

This is extremely important.

Never blindly run:

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst';

just because a session is blocking others.

First determine:

  • Who owns the session?
  • Which application?
  • Is it a critical transaction?
  • How long has it been running?
  • What transaction is active?
  • How many sessions are blocked?
  • Is rollback going to be expensive?
  • Is the session already disconnected from the application?
  • Is there a safer application-side solution?

47. If You Must Kill It

In RAC, the syntax includes the instance:

ALTER SYSTEM KILL SESSION '101,12345,@1';

The exact action should be based on your operational procedures.

Possible alternatives include:

DISCONNECT SESSION

or allowing the application to resolve the transaction.

The key point is:

Killing the blocker is an operational decision, not a diagnostic shortcut.

48. Why Killing a Session Can Make Things Worse

Suppose:

Transaction:
UPDATE 2 million rows

You kill it.

Oracle may need to roll back a large transaction.

That rollback can:

  • Consume CPU
  • Generate I/O
  • Generate undo activity
  • Take significant time
  • Continue affecting application performance

So:

A session that is blocking users may also be holding a large transaction whose rollback is expensive.

Always investigate before acting.

49. A Production Troubleshooting Sequence

When users report:

“The application is hanging.”

I recommend:

1. Check GV$SESSION
        ↓
2. Find waiters
        ↓
3. Find blockers
        ↓
4. Identify root blocker
        ↓
5. Identify SQL
        ↓
6. Identify transaction
        ↓
7. Identify application/module
        ↓
8. Identify affected object
        ↓
9. Check RAC instance
        ↓
10. Decide corrective action

50. Example: Real RAC Investigation

Imagine this output:

WAITING SESSION

Instance: 2
SID: 500
Service: SALES_APP
SQL_ID: 9abc123
Event: enq: TX - row lock contention
Wait: 1,200 sec

The blocker:

Instance: 1
SID: 200
Service: BATCH
SQL_ID: 4xyz789
Status: INACTIVE

At first:

“Batch session is inactive.”

But transaction information shows:

Transaction age: 45 minutes
Undo blocks: 800,000

Now the root cause is much clearer:

Batch
  ↓
Long-running uncommitted transaction
  ↓
Locks rows
  ↓
SALES_APP waits
  ↓
Cross-instance contention

This is an application/workload management problem.

51. How to Explain This to Management

Don’t say:

“RAC is causing blocking.”

That’s technically misleading.

A better incident statement is:

“The SALES application experienced transaction lock contention because a long-running batch transaction on the first RAC instance retained row locks required by OLTP sessions on the second instance.”

That’s the language of a senior DBA.

52. RAC Locking Checklist

During a locking incident:

Session
  • SID
  • SERIAL#
  • INST_ID
  • USERNAME
  • MACHINE
  • PROGRAM
  • MODULE
  • SERVICE
SQL
  • SQL_ID
  • SQL text
  • Execution plan
  • Duration
  • Executions
Lock
  • Lock type
  • ID1
  • ID2
  • LMODE
  • REQUEST
  • BLOCK
Transaction
  • Start time
  • Undo usage
  • Transaction age
RAC
  • Local or remote blocker
  • Instance
  • Service
  • GES activity
Application
  • Batch?
  • OLTP?
  • Connection pool?
  • Missing commit?
  • Long transaction?

53. Expert Mental Model

At this stage, your RAC architecture should look like this:

                         APPLICATION
                              |
                           SERVICE
                              |
                 +------------+------------+
                 |                         |
               ORCL1                     ORCL2
                 |                         |
             SESSION A                 SESSION B
                 |                         |
             TX / TM                    TX / TM
                 |                         |
                 +-----------+-------------+
                             |
                            GES
                             |
                  Global Enqueue Coordination

And for data blocks:

                 SESSION
                    |
                   SQL
                    |
                  BLOCK
                    |
                   GCS
                    |
               Cache Fusion

Therefore:

LOCK PROBLEM
     ↓
GES

BLOCK TRANSFER
     ↓
GCS

That distinction will save you a lot of troubleshooting time.

54. Part 17 Summary

Today we covered:

  • GES architecture
  • GCS vs GES
  • RAC global locking
  • TX enqueues
  • TM enqueues
  • GV$SESSION
  • GV$LOCK
  • Blocking sessions
  • Root blockers
  • Long-running transactions
  • Idle sessions holding locks
  • Cross-instance blocking
  • Historical blocking with ASH
  • Deadlocks
  • ORA-00060
  • GES diagnostic views
  • Production-safe session termination
  • Building a RAC blocking report

The key principle is:

Don’t just find the blocked session. Find the transaction that owns the resource, understand why it remains open, and determine why the application created the contention.

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply