How to Remove a Deadlock in Oracle by Killing the SQL Statement

A deadlock occurs when two or more sessions hold locks on resources that the other sessions are waiting for, causing a circular wait. Oracle automatically detects deadlocks and resolves one of the sessions, but sometimes, you may need to intervene manually.

This guide shows how to identify the deadlock and remove it safely by killing the offending SQL statement.

---

1. Identify Blocking Sessions

First, find out which sessions are blocking others:

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

• blocking_session → SID of the session causing the block
• sql_id → SQL statement currently executing

---

2. Check the SQL Statement Causing the Deadlock

Once you have the SQL_ID of the blocking session, check its query:

SELECT sql_id,
       sql_text
FROM   v$sql
WHERE  sql_id = '<SQL_ID>';

3. Check the Status of the Transaction

Make sure the session is active before killing the SQL:

SELECT s.sid,
       s.serial#,
       s.username,
       t.start_time,
       t.status AS txn_status
FROM   gv$transaction t
JOIN   gv$session s
ON     t.addr = s.taddr
WHERE  s.sql_id = '<SQL_ID>';

• ACTIVE → Currently running transaction
• INACTIVE → Idle, but holding locks

4. Cancel (Kill) Only the SQL Statement

Instead of killing the whole session, Oracle 12c+ allows cancelling just the SQL:

ALTER SYSTEM CANCEL SQL '<SQL_ID>';

Example:

ALTER SYSTEM CANCEL SQL '8u3b5z3qz6c2s';

Cancel Using SID & SERIAL#

ALTER SYSTEM CANCEL SQL '<SQL_ID>', <SID>, <SERIAL#>;

Example:
ALTER SYSTEM CANCEL SQL '8u3b5z3qz6c2s', 123, 45678;

• Find SID & SERIAL# with:

SELECT sid, serial#
FROM v$session
WHERE sql_id = '8u3b5z3qz6c2s';

5. Verify if the SQL is Killed

Check session status and SQL_ID:

SELECT sid,
       serial#,
       status,
       sql_id
FROM v$session
WHERE sid = <SID>;

6. Kill the Session (If SQL Cancel Does Not Work)

If cancelling SQL doesn’t resolve the deadlock:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>';

Example:
ALTER SYSTEM KILL SESSION '123,45678';
OR
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;

7. Check for Remaining Blockers

After cancelling the SQL or killing the session, check for any remaining blockers:

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

8. Use GV$ for fixing RAC Blocking

In RAC environments, include INST_ID:

SELECT inst_id, sid, serial#, username, blocking_session, sql_id, status
FROM gv$session
WHERE blocking_session IS NOT NULL;

Kill session with:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>,@<INST_ID>' IMMEDIATE;

Best Practices to Prevent Deadlocks

1. Access objects in the same order in all applications
2. Keep transactions short
3. Avoid manual locks unless necessary
4. Monitor blocking sessions regularly
5. Use row-level locks instead of table locks if possible

Summary Table of Commands

Action	Command
Find Blocking Sessions	SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
Check SQL Text	SELECT sql_text FROM v$sql WHERE sql_id='<SQL_ID>';
Check Transaction Status	SELECT * FROM gv$transaction JOIN gv$session ...
Cancel SQL	ALTER SYSTEM CANCEL SQL '<SQL_ID>';
Cancel SQL with SID/SERIAL#	ALTER SYSTEM CANCEL SQL '<SQL_ID>', SID, SERIAL#;
Kill Session	ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
Kill Session Immediate	ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
Check Remaining Blockers	SELECT * FROM v$session WHERE blocking_session IS NOT NULL;