SQL Lock Storm on Queries After Mass Update

Your queries are hanging. Here's the fix.

You ran a mass update on a big table, and now every other query times out. Don't restart the server yet. The culprit is almost always a single session that grabbed locks and never let go.

Step 1: Find the blocking session

In Oracle (same idea works on SQL Server with sys.dm_exec_requests):

SELECT 
  sid, 
  serial#, 
  username, 
  program, 
  blocking_session 
FROM v$session 
WHERE blocking_session IS NOT NULL;

This shows you who's blocking who. The session with blocking_session = NULL but other sessions blocking it is your main blocker.

Step 2: Kill the blocker

Once you have the SID and SERIAL#:

ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

Replace 123 with the SID, 456 with the SERIAL#. The IMMEDIATE part is key — without it, Oracle waits for the session to finish its current work, which can take forever.

Why this works

Your mass update ran as one big transaction. It locked rows (or worse, escalated to a table lock). Other queries wanted those same rows — they queued up behind it. Killing the blocker rolls back the transaction and releases all locks. Queries start running again within seconds.

When killing the session doesn't work

Sometimes the session won't die — it's stuck doing something Oracle can't interrupt. Then you need to:

1. Find the OS process ID from v$session and v$process.
2. Kill it at the OS level: kill -9 <PID> (Linux) or taskkill /F /PID <PID> (Windows).
3. Then do ALTER SYSTEM KILL SESSION ... IMMEDIATE again to clean up Oracle side.

Rarely, you'll need to bounce the instance. Skip that unless you've got no other option — it's a sledgehammer.

Why the lock storm happened in the first place

Your mass update lacked proper batching. A single UPDATE table SET col = value WHERE condition on 500k rows locks every row until the whole thing commits. That's a recipe for a storm.

How to prevent it next time

Break the update into chunks. Here's a pattern I've used for years:

DECLARE
  v_rows_updated NUMBER := 1;
BEGIN
  WHILE v_rows_updated > 0 LOOP
    UPDATE table 
    SET col = value 
    WHERE condition 
    AND ROWNUM <= 1000;
    v_rows_updated := SQL%ROWCOUNT;
    COMMIT;
    -- small pause to let other queries in
    DBMS_LOCK.SLEEP(0.1);
  END LOOP;
END;

Chunk size of 500-1000 rows works well. Adjust based on your row width and concurrency. Commit after each chunk. The SLEEP call isn't mandatory but helps on busy systems.

Other quick wins

• Index on the WHERE column — without it, the update locks way more rows than needed.
• Use row-level locking hints like SELECT ... FOR UPDATE WAIT 5 in your app code to avoid indefinite waits.
• Set lock timeout at session level: ALTER SESSION SET ddl_lock_timeout = 10 (Oracle 18c+).

When it's not your update

Sometimes the blocker is a long-running report or a backup job. Same steps apply: find the blocker, kill it. Then talk to whoever owns that job about scheduling it off-peak.

Bottom line

Lock storms from batch updates are common and fixable without panic. Find the blocker session, kill it, and learn to batch your updates. Do that and you won't see this error again. I've fixed this for a dozen companies — it's always the same root cause.