# Diagnosing an Oracle Blocking Chain: From Application Symptom to Root Cause

The ticket always reads the same way: "the app is hanging." Someone pulls up `V$SESSION`, finds the stuck session, finds whoever is blocking it, and goes to have a conversation with that person's code. Sometimes that's the right conversation. Sometimes it isn't, because the session you found isn't the problem, it's just the last link in a chain, itself waiting on someone else entirely.

I wanted a scenario where that distinction is unavoidable rather than theoretical, so I built one: three sessions, a genuine transitive block (C waits on B, B waits on A), and a fourth session doing nothing but diagnostics. Everything below uses base `V$` views, no AWR, no ASH, nothing that needs the Diagnostic or Tuning Pack, so it applies the same on Standard Edition 2 as Enterprise.

## The two facts that make blocking chains confusing

**A session can be a waiter and a blocker at the same time.** Being blocked on something doesn't release whatever locks that session already holds. This is the entire mechanism behind a transitive chain, and it's easy to forget when you're staring at one session's wait event in isolation.

`V$LOCK.TYPE` **tells you what kind of problem you actually have.** `TX` is a row-level transaction lock two sessions fighting over the same row. `TM` is a table-level DML lock often taken as a side effect of something that has nothing to do with row contention at all (more on this below). They look identical from the application's side ("my request just hangs") and are diagnosed and fixed completely differently.

## Building a real chain, not just a block

Two sessions blocking each other is easy to produce and doesn't teach much. The more useful (and more realistic) scenario is three sessions:

```sql
CREATE TABLE orders (
  order_id       NUMBER PRIMARY KEY,
  status         VARCHAR2(20) NOT NULL,
  customer_name  VARCHAR2(50) NOT NULL
);

INSERT INTO orders VALUES (1, 'NEW', 'Acme Corp');
INSERT INTO orders VALUES (2, 'NEW', 'Globex Inc');
COMMIT;
```

**Session A** (Terminal 1):

```sql
UPDATE orders SET status = 'A_TOUCHED' WHERE order_id = 1;
-- no commit
```

**Session B** (Terminal 2) — locks a different row first, then reaches for A's row:

```sql
UPDATE orders SET status = 'B_TOUCHED' WHERE order_id = 2;  -- succeeds, no commit
UPDATE orders SET status = 'B_WANTS_1' WHERE order_id = 1;  -- HANGS, blocked on A
```

**Session C** (Terminal 3) — reaches for the row B is holding, while B is itself stuck:

```sql
UPDATE orders SET status = 'C_WANTS_2' WHERE order_id = 2;  -- HANGS, blocked on B
```

At this point: C is visibly hung. If you only check "who's blocking C," you'll find B, and B looks like a perfectly reasonable place to start debugging it's holding a lock and not releasing it. But B is *also* stuck, waiting on A. The actual root cause is two hops away from the symptom, and B did nothing wrong other than getting in line behind A.

## Walking the chain instead of stopping at the first link

This is the query that matters most. `V$SESSION.BLOCKING_SESSION` has been available and auto-populated since 10gR2 no setup required and a `CONNECT BY` on it gives you the entire chain, correctly nested, in one shot:

```sql
SELECT LPAD(' ', 2*(LEVEL-1)) || sid AS sid_tree,
       serial#, username, status, blocking_session, event, seconds_in_wait
FROM   v$session
WHERE  type != 'BACKGROUND'
START WITH blocking_session IS NULL
CONNECT BY PRIOR sid = blocking_session
ORDER SIBLINGS BY sid;
```

Root blockers (nobody blocking them) land at the top with no indentation; everyone waiting on them nests underneath, however many levels deep. For the scenario above, this prints A at the root, B indented once under A, and C indented twice under B the whole chain, structurally, without you having to manually trace anything.

If you're coming at it from the incident-response direction instead someone hands you a specific SID and says "this is stuck" flip the query around and walk *up* from that SID to the root:

```sql
SELECT LEVEL AS hop, sid, blocking_session, event, seconds_in_wait
FROM   v$session
WHERE  type != 'BACKGROUND'
START WITH sid = &symptom_sid
CONNECT BY PRIOR blocking_session = sid
ORDER BY hop;
```

Two more queries round out the picture. First, what is the root blocker actually *doing*? A session that's just sitting there holding a lock (waiting on the next keystroke from its own client, from Oracle's point of view) has a null `SQL_ID` you want its *previous* statement:

```sql
SELECT s.sid, s.username, s.last_call_et, q.sql_fulltext
FROM   v$session s
LEFT JOIN v$sql q ON q.sql_id = s.prev_sql_id
WHERE  s.sid IN (SELECT DISTINCT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);
```

Second, how long has the root transaction actually been open? This is the query that turns "the database feels slow" into "someone has had a transaction open for 40 minutes," which is a completely different conversation to have with a team:

```sql
SELECT s.sid, t.start_time,
       ROUND((SYSDATE - TO_DATE(t.start_time,'MM/DD/RR HH24:MI:SS')) * 86400) AS seconds_open
FROM   v$transaction t
JOIN   v$session s ON s.taddr = t.addr;
```

Once A commits, re-run the tree query it doesn't go empty, it shrinks by exactly one link: C is now *directly* blocked by B, who is no longer blocked by anything. Only once B also commits does the whole thing clear. Watching that shrink one link at a time is, honestly, the most convincing way to internalize "blocked sessions can hold their own locks" more convincing than reading it in a sentence like this one.

## The look-alike: an unindexed foreign key

Here's a scenario that produces an identical-sounding symptom "my insert just hangs, and I'm not touching anything anyone else is touching" through a completely different mechanism.

```sql
-- ORDER_ITEMS.ORDER_ID references ORDERS.ORDER_ID, but has NO index
```

**Session A** deletes a parent row that currently has *no* child rows (so there's no actual FK violation):

```sql
DELETE FROM orders WHERE order_id = 4;  -- no children, succeeds -- no commit
```

**Session B** inserts a child row for a *completely different* order:

```sql
INSERT INTO order_items (item_id, order_id, sku, qty) VALUES (201, 2, 'WIDGET-D', 1);
-- HANGS -- order_id 2 has nothing to do with order_id 4
```

Without an index on the FK column, Oracle can't cheaply verify "does anything reference order\_id=4" so instead of a targeted check, it takes a `SHARE` lock on the *entire* `ORDER_ITEMS` table for the rest of A's transaction, to guarantee nobody can insert a new child row that would race against the delete. Every other insert into that table blocks, regardless of which order it's for. Check `V$LOCK.TYPE` here and you'll see `TM`, not `TX` the tell that you're looking at a referential-integrity lock, not two sessions fighting over a row.

The fix is one line, and it's arguably the single most common "free" concurrency win in an inherited schema, because Oracle does not create this index automatically the way it does for the primary key the FK references:

```sql
CREATE INDEX ix_order_items_order_id ON order_items(order_id);
```

With the index in place, Oracle can answer "does anything reference order 4" with a targeted probe instead of a table-wide guarantee, and the unrelated insert never blocks at all. Worth running once against a real schema:

```sql
SELECT c.table_name, c.constraint_name, cc.column_name
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND NOT EXISTS (
  SELECT 1 FROM user_ind_columns i
  WHERE i.table_name = c.table_name
  AND i.column_name = cc.column_name
  AND i.column_position = 1
);
```

This lists every foreign key in the schema whose leading column isn't the leading column of any index. It tends to turn up more hits than people expect.

## An application-level fix, for the pattern that started this whole post

Diagnosing a blocking chain after the fact is a skill worth having. Preventing an entire *category* of them is better. The chain this post opened with is a specific, common shape: multiple workers contending for rows to process a queue table, a batch of orders, anything with a "claim the next one" pattern.

The naive version:

```sql
SELECT order_id, status FROM orders
WHERE status = 'NEW' ORDER BY order_id
FOR UPDATE FETCH FIRST 1 ROW ONLY;
```

If Worker 1 has already claimed `order_id = 1`, Worker 2 running this exact query **hangs**, waiting for row 1's lock to clear even though `order_id = 2` is sitting there completely free. Plain `FOR UPDATE` locks rows in the order it visits them; it can't skip ahead to a free one on its own. Worker 2 eventually gets the right answer (Oracle re-checks the `WHERE` clause once row 1 unblocks, sees it no longer matches `status = 'NEW'`, and moves on) but only after waiting out Worker 1's *entire* processing time, for no reason.

One keyword fixes it:

```sql
SELECT order_id, status FROM orders
WHERE status = 'NEW' ORDER BY order_id
FOR UPDATE SKIP LOCKED
FETCH FIRST 1 ROW ONLY;
```

`SKIP LOCKED` tells Oracle: if the next candidate row is locked, act as if it isn't there and move to the next one that's free. Worker 2 gets `order_id = 2` immediately, no wait, regardless of what Worker 1 is doing. This is nearly always the behavior you want for concurrent job/queue processing, and it eliminates the blocking chain before it can start rather than requiring anyone to diagnose one after the fact.

It's worth being honest about what this does and doesn't fix. It would have done nothing for this post's opening scenario two sessions genuinely need to update the *same* row, and that's a real business-logic conflict, not a scheduling inefficiency. The fix there is transactional discipline: commit or roll back promptly, and don't hold a lock across unrelated work or user think-time in the same transaction. Three symptoms in this post, three different root causes, three different fixes that's really the whole point.

## Checklist for your own systems

*   Run the chain-walking query as a periodic health check, not just during an incident it's cheap and licensing-free.
    
*   Run the unindexed-FK query above once against a schema you've inherited. It usually finds something.
    
*   Grep your codebase for `FOR UPDATE` without `SKIP LOCKED` anywhere near queue- or worker-style processing.
    
*   When you find a blocking chain, write down both fixes separately: how you unblocked *this specific incident*, and what code or schema change stops *this shape* of incident from recurring. They're often not the same fix.
    

## Try it yourself

The full lab four terminals, a precise step-by-step runbook, and the two bonus modules is about 60 lines of DDL/DML plus a dozen short diagnostic queries, and it runs the same on a docker 19c instance as anything else. Reproducing a blocking chain on purpose, on a table you don't care about, is a much better way to build the instinct for "keep walking up the chain" than trying to remember it the next time production is actually stuck.