Oracle Blocking Sessions Query: Find and Fix Lock Waits

To find blocking sessions in Oracle, query V$SESSION for rows where BLOCKING_SESSION is not null. Each row is a session that is stuck waiting, and the BLOCKING_SESSION column tells you the SID of the session holding the lock it needs.

That one query answers the urgent question. This tutorial goes further: you will build a real blocking situation on a small table, read the output column by column, find the root blocker in a chain, see which table and row are locked, and learn the safe ways to clear the block. The queries use views that exist in Oracle 19c and later releases.

How blocking works in Oracle

When a transaction runs INSERT, UPDATE, DELETE, MERGE or SELECT ... FOR UPDATE, Oracle takes a row lock (a TX lock) on every row it changes. That lock stays until the transaction commits or rolls back. It does not expire on its own.

If a second session tries to change one of those same rows, it has to wait. The waiting session is blocked, and the session holding the lock is the blocker. While it waits, the second session shows the wait event enq: TX - row lock contention.

A few rules from Oracle's concurrency model help you reason about what you see:

• A writer blocks another writer of the same row. Different rows in the same table do not block each other.
• A plain SELECT never blocks a writer, and a writer never blocks a plain SELECT. Readers get a consistent older version of the row instead.
• SELECT ... FOR UPDATE does lock rows, so it can block and be blocked like an UPDATE.
• Oracle never escalates row locks to a table lock, no matter how many rows you lock.

So almost every blocking problem has the same shape: one transaction changed some rows and has not ended yet. The most common cause is an application, script or SQL tool that ran DML and never committed.

Sample setup

Create a small orders table and add a few rows. You need three separate connections (three SQL*Plus or SQL Developer sessions) to reproduce the block, plus one more connection with access to the V$ views to watch it.

CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  customer     VARCHAR2(50),
  status       VARCHAR2(20),
  amount       NUMBER(10,2)
);

INSERT INTO orders VALUES (101, 'Asha',  'NEW',     2500);
INSERT INTO orders VALUES (102, 'Ravi',  'NEW',     1800);
INSERT INTO orders VALUES (103, 'Meena', 'SHIPPED', 4200);
COMMIT;

Now create the block. Run each statement in the session shown, and do not commit in any of them.

-- Session 1: updates order 101 and does NOT commit
UPDATE orders SET status = 'PAID' WHERE order_id = 101;

-- Session 2: tries to update the same row. It hangs.
UPDATE orders SET amount = 2600 WHERE order_id = 101;

-- Session 3: also wants order 101. It hangs too.
DELETE FROM orders WHERE order_id = 101;

Session 1 returns at once with "1 row updated". Sessions 2 and 3 appear frozen. They are not broken, they are waiting for Session 1 to commit or roll back.

Step 1: find blocked sessions with V$SESSION

From your monitoring connection, run the basic blocking sessions query:

SELECT sid,
       serial#,
       username,
       blocking_session,
       event,
       ROUND(wait_time_micro / 1e6) AS wait_secs,
       sql_id
  FROM v$session
 WHERE blocking_session IS NOT NULL
 ORDER BY wait_secs DESC;

Output (expected output). Your SID, SERIAL# and SQL_ID values will differ.

Read it like this: sessions 72 and 88 are waiting, and both point to session 45. Session 45 is the blocker. WAIT_SECS shows how long each one has been in its current wait, and SQL_ID identifies the statement that is stuck.

The BLOCKING_SESSION value is only meaningful when BLOCKING_SESSION_STATUS is VALID. The other documented values are NO HOLDER (nobody is blocking this session), NOT IN WAIT (the session is not waiting) and UNKNOWN.

If this query returns no rows, nothing is blocked right now. Slowness at that moment has some other cause.

Step 2: look at the blocking session

Now find out who session 45 is and what it is doing. This query lists every session that is blocking at least one other session:

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.machine,
       s.program,
       s.event,
       s.last_call_et AS secs_in_status,
       s.prev_sql_id
  FROM v$session s
 WHERE s.sid IN (SELECT blocking_session
                   FROM v$session
                  WHERE blocking_session IS NOT NULL);

Output (expected output):

This is the classic picture. The blocker is INACTIVE and waiting on SQL*Net message from client, which means it finished its last statement and is waiting for the client to send something. It is idle while still holding a lock. The client ran the UPDATE and never committed.

LAST_CALL_ET is the number of seconds the session has been in its current status, so here it has sat idle for over two minutes. SQL_ID is usually empty for an idle session, which is why the query shows PREV_SQL_ID, the last statement it ran. Use MACHINE and PROGRAM to find the person or application behind it.

To see the text of that last statement, look it up in V$SQL:

SELECT sql_text
  FROM v$sql
 WHERE sql_id = '&prev_sql_id';

If the blocker's STATUS is ACTIVE instead, it is still working, for example a long batch update. Killing it would throw away that work and start a rollback, so check with the owner first.

Step 3: find the root blocker in a chain

Blocking can form chains. Session A blocks B, and B, while waiting, holds locks that C needs. The direct blocker of C is B, but the session you need to deal with is A. Oracle tracks this for you in FINAL_BLOCKING_SESSION:

SELECT sid,
       blocking_session,
       final_blocking_session,
       event
  FROM v$session
 WHERE final_blocking_session_status = 'VALID';

Output (expected output) for our setup, where both waiters are blocked by the same session:

In a longer chain, BLOCKING_SESSION and FINAL_BLOCKING_SESSION differ, and the final one is the root. For a picture of the whole tree, use a hierarchical query:

SELECT LPAD(' ', 2 * (LEVEL - 1)) || sid AS session_tree,
       username,
       status,
       event
  FROM v$session
 START WITH sid IN (SELECT blocking_session
                      FROM v$session
                     WHERE blocking_session IS NOT NULL)
        AND blocking_session IS NULL
CONNECT BY PRIOR sid = blocking_session;

Output (expected output):

SESSION_TREE  USERNAME  STATUS    EVENT
------------  --------  --------  -----------------------------
45            APP_USER  INACTIVE  SQL*Net message from client
  72          APP_USER  ACTIVE    enq: TX - row lock contention
  88          APP_USER  ACTIVE    enq: TX - row lock contention

The START WITH clause picks sessions that block others but are not blocked themselves, which are the root blockers. CONNECT BY then walks down to everyone waiting on them. Indentation shows the depth.

Step 4: confirm the lock with V$LOCK

V$LOCK shows the locks themselves. A holder has BLOCK = 1 (its lock is blocking others), and a waiter has REQUEST > 0 (it is asking for a lock mode it has not got). Join them on the lock identity, which is TYPE, ID1 and ID2:

SELECT h.sid   AS holder_sid,
       w.sid   AS waiter_sid,
       h.type,
       h.lmode AS held_mode,
       w.request AS requested_mode
  FROM v$lock h
  JOIN v$lock w
    ON  w.type = h.type
   AND w.id1  = h.id1
   AND w.id2  = h.id2
 WHERE h.block   = 1
   AND w.request > 0;

Output (expected output):

Type TX is a transaction (row) lock. Mode 6 is exclusive. So session 45 holds the transaction lock exclusively, and sessions 72 and 88 are asking for it in the same mode. They can only get it when session 45's transaction ends.

You will also see TM rows (DML table locks) for every session doing DML. Those are normal and do not mean anything is blocked. That is why you should never treat "V$LOCK returned rows" as proof of blocking. Filter on BLOCK and REQUEST as shown.

Step 5: find the locked table and row

A waiting session records what it is waiting for in ROW_WAIT_OBJ# and related columns. Join to DBA_OBJECTS to get the table name:

SELECT s.sid,
       o.owner,
       o.object_name,
       s.row_wait_file#,
       s.row_wait_block#,
       s.row_wait_row#
  FROM v$session s
  JOIN dba_objects o
    ON o.object_id = s.row_wait_obj#
 WHERE s.blocking_session IS NOT NULL;

Output (expected output). File, block and row numbers will differ on your system.

To see the exact row, rebuild its ROWID with DBMS_ROWID.ROWID_CREATE. Two details trip people up here. The function wants the data object number (DATA_OBJECT_ID), not OBJECT_ID, and it wants the relative file number, while ROW_WAIT_FILE# is the absolute file number. DBA_DATA_FILES maps one to the other:

SELECT s.sid,
       DBMS_ROWID.ROWID_CREATE(1,
                               o.data_object_id,
                               f.relative_fno,
                               s.row_wait_block#,
                               s.row_wait_row#) AS locked_rowid
  FROM v$session s
  JOIN dba_objects    o ON o.object_id = s.row_wait_obj#
  JOIN dba_data_files f ON f.file_id   = s.row_wait_file#
 WHERE s.blocking_session IS NOT NULL;

Then read the row with that ROWID, as the table owner:

SELECT *
  FROM orders
 WHERE ROWID = '&locked_rowid';

Output (expected output):

Notice the row still shows NEW, not PAID. You are reading the committed version, because Session 1's change is not committed yet. That is the same consistent-read behaviour that lets plain queries run while rows are locked.

Step 6: resolve the block safely

You have three options. Try them in this order.

Option 1: ask the owner to commit or roll back

This is the cleanest fix. If the person in Session 1 runs COMMIT or ROLLBACK, the lock is released and the waiters continue immediately. Use USERNAME, MACHINE and PROGRAM from Step 2 to find them. Nothing is lost and nothing is guessed.

Option 2: kill the blocking session

If the owner cannot be reached, a user with the ALTER SYSTEM privilege can kill the session. You need both the SID and the SERIAL# from Step 2:

ALTER SYSTEM KILL SESSION '45,20511' IMMEDIATE;

Killing a session rolls back its open transaction and releases its locks. IMMEDIATE tells Oracle to do this and return control to you right away. Without it, if the session is busy with something that must finish first, such as a large rollback, Oracle waits up to about a minute and then returns a message saying the session is marked to be killed. The cleanup then finishes in the background.

On Oracle RAC, add the instance ID so you kill the session on the right node. Take INST_ID from GV$SESSION:

ALTER SYSTEM KILL SESSION '45,20511,@2' IMMEDIATE;

Option 3: disconnect after the transaction ends

If the blocker is doing valid work and you only want it gone afterwards, DISCONNECT SESSION ... POST_TRANSACTION lets the current transaction finish before the session is disconnected:

ALTER SYSTEM DISCONNECT SESSION '45,20511' POST_TRANSACTION;

This does not help an idle session that never commits, because its transaction never finishes. Use it for active work you want to end cleanly.

Find blocking sessions from the past

V$SESSION only shows what is happening now. If users complain about a freeze that already cleared, use Active Session History. V$ACTIVE_SESSION_HISTORY samples active sessions once a second and keeps the blocker for each wait:

SELECT sample_time,
       session_id,
       blocking_session,
       blocking_session_serial#,
       event,
       sql_id
  FROM v$active_session_history
 WHERE blocking_session IS NOT NULL
   AND sample_time > SYSTIMESTAMP - INTERVAL '30' MINUTE
 ORDER BY sample_time;

Older history lives in DBA_HIST_ACTIVE_SESS_HISTORY, which keeps a subset of these samples for longer.

Common mistakes

• Killing the wrong session. People see the waiting session in the first query and kill it. That only cancels the victim's work. Kill the session in BLOCKING_SESSION or FINAL_BLOCKING_SESSION, never the waiter.
• Using only the SID. SIDs are reused. If the blocker disconnects and a new session gets the same SID, a kill command with an old serial number fails safely, but guessing the serial number can hit an innocent session. Always copy SID and SERIAL# from the same fresh query.
• Reading every V$LOCK row as a problem. Every DML statement takes locks. Only BLOCK = 1 with a matching REQUEST > 0 waiter means blocking.
• Confusing blocking with deadlock. Blocking lasts until the blocker ends its transaction. A deadlock is two sessions waiting on each other. Oracle detects deadlocks itself and raises ORA-00060 in one of the sessions, so you do not kill anything for a deadlock.
• Leaving autocommit off in a GUI tool and walking away. An UPDATE run in SQL Developer or Toad and left uncommitted holds its row locks until the session ends. This is a very common source of idle blockers.
• Querying V$SESSION on one node of a RAC database. V$SESSION shows only the local instance. Use GV$SESSION with BLOCKING_INSTANCE to see blockers on other nodes.

Best practices to prevent blocking

• Keep transactions short. Do the work, then commit. Do not hold a transaction open while waiting for user input or a slow external call.
• Commit or roll back in every code path, including error handlers in PL/SQL and in the application.
• Index foreign key columns. Without an index on the child table's foreign key, deleting or updating a parent key can lock the child table and block other DML on it.
• Use SELECT ... FOR UPDATE NOWAIT or WAIT n when an application should fail fast instead of hanging. With NOWAIT, Oracle raises ORA-00054 straight away if the rows are locked.
• For job queue style processing, SELECT ... FOR UPDATE SKIP LOCKED lets each worker take rows that nobody else has locked.
• Set MODULE and ACTION with DBMS_APPLICATION_INFO in your code. It makes the blocker in Step 2 much easier to trace back to a program.

Frequently asked questions

How do I check for locks on a table in Oracle?

Query V$LOCKED_OBJECT joined to DBA_OBJECTS on OBJECT_ID. It lists every session that currently holds a lock on the object. Holding a lock is normal during DML; it only becomes a problem when another session is waiting, which Step 1 shows.

Does a SELECT statement block other sessions in Oracle?

No. A plain SELECT takes no row locks and is never blocked by writers, because it reads a consistent version of the data. Only SELECT ... FOR UPDATE locks rows.

What does BLOCKING_SESSION_STATUS 'NO HOLDER' mean?

It means the session is waiting, but no session is blocking it. It is waiting on something other than another session's lock, so BLOCKING_SESSION is empty.

How do I find blocking sessions in Oracle RAC?

Query GV$SESSION instead of V$SESSION and select INST_ID, BLOCKING_INSTANCE and BLOCKING_SESSION. The blocker may be on a different instance from the waiter. Use the @inst_id form of KILL SESSION to target it.

Will the blocked session get an error?

Not by default. A normal UPDATE or DELETE waits until the lock is released, however long that takes. When the blocker commits or rolls back, the waiter carries on. If the blocker is killed, its transaction is rolled back and the waiter continues.

Summary

• Find waiters with SELECT ... FROM v$session WHERE blocking_session IS NOT NULL.
• Check the blocker's STATUS, EVENT, PREV_SQL_ID, MACHINE and PROGRAM. INACTIVE plus SQL*Net message from client usually means an uncommitted transaction.
• Use FINAL_BLOCKING_SESSION or a CONNECT BY query to find the root of a chain.
• Use ROW_WAIT_OBJ# and DBMS_ROWID to see the locked table and row.
• Ask the owner to commit or roll back first. Kill only the blocker, with SID and SERIAL#, after checking what it is doing.
• Use ASH for past incidents only if you are licensed for the Diagnostics Pack.
• Prevent blocking with short transactions, indexed foreign keys and NOWAIT or SKIP LOCKED where they fit.