Oracle Deadlocks
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
Demo
Deadlocks Demo

Open 3 separate SQL*Plus terminal sessions for this demo.
-- session 1
conn uwclass/uwclass@pdborcl

CREATE TABLE deadlock (
id NUMBER, fld VARCHAR2(1));

INSERT INTO deadlock VALUES (1,'A');
INSERT INTO deadlock values (2,'B');
COMMIT;

SELECT * FROM deadlock;

UPDATE deadlock
SET fld = 'M'
WHERE id = 1;

-- session 2
conn uwclass/uwclass@pdborcl

UPDATE deadlock
SET fld = 'N'
WHERE id = 2;

-- session 1
UPDATE deadlock
SET fld = 'X'
WHERE id = 2;

-- session as SYS
conn / as sysdba

SELECT (
  SELECT username
  FROM gv$session
  WHERE sid=a.sid) blocker,
  a.sid, ' is blocking ', (
  SELECT username
  FROM gv$session
  WHERE sid=b.sid) blockee,
  b.sid
FROM gv$lock a, gv$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2;

-- session 2
UPDATE DEADLOCK
SET fld = 'Y'
WHERE id = 1;

SQL> ORA-00060: deadlock detected while waiting for resource

ROLLBACK;
 
General SELECT FOR UPDATE
View for viewing locks conn / as sysdba

GRANT SELECT ON dba_lock TO uwclass;
GRANT SELECT ON v_$mystat TO uwclass;

conn uwclass/uwclass

SELECT sid FROM v$mystat WHERE rownum = 1;

set linesize 121
col object_name format a20
col lock_type format a15
col mode_held format a15
col mode_requested format a20
col blocking_others format a20

CREATE OR REPLACE VIEW locked_objs AS
SELECT o.object_name, l.lock_type, l.mode_held,
l.mode_requested, l.blocking_others
FROM dba_lock l, user_objects o
WHERE l.lock_id1 = o.object_id
AND session_id = 139;
FOR UPDATE locking demo SELECT *
FROM locked_objs;

SELECT *
FROM deadlock;

SELECT *
FROM locked_objs;

SELECT *
FROM deadlock
FOR UPDATE;

SELECT *
FROM locked_objs;

COMMIT;

SELECT *
FROM locked_objs;

SELECT *
FROM deadlock
FOR UPDATE;

SELECT *
FROM locked_objs;

ROLLBACK;

SELECT *
FROM locked_objs;
 
Selective SELECT FOR UPDATE
SELECT FOR UPDATE with WHERE clause
Session 1 Session 2
CREATE TABLE deadlock (
id NUMBER, fld VARCHAR2(1));

INSERT INTO deadlock VALUES (1,'A');
INSERT INTO deadlock values (2,'B');
INSERT INTO deadlock VALUES (3,'C');
INSERT INTO deadlock values (4,'D');
COMMIT;

SELECT *
FROM deadlock;

SELECT *
FROM locked_objs;

SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE;

SELECT *
FROM locked_objs;
 
  UPDATE deadlock
SET fld = 'Z'
WHERE id = 3;

UPDATE deadlock
SET fld = 'Z'
WHERE id = 1;
ROLLBACK;  
 
FOR UPDATE with NOWAIT
NOWAIT Demo

-- continuing from above demo
SELECT <column_names>
FROM <table_name>
FOR UPDATE NOWAIT;
Session 1 Session 2
  ROLLBACK;
SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE;
 
  SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE NOWAIT;
 
FOR UPDATE with WAIT
WAIT Demo

-- continuing from above demo
SELECT <column_names>
FROM <table_name>
FOR UPDATE WAIT <wait_period_in_seconds>;
Session 1 Session 2
  SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE WAIT;

SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE WAIT 5;
 
FOR UPDATE with NOWAIT SKIP LOCKED
Skip Locked Demo

-- continuing from above demo
-- this is an undocumented feature in versions prior to 11.2.0.1 but is now fully supported

SELECT <column_names>
FROM <table_name>
FOR UPDATE NOWAIT SKIP LOCKED;
Session 1 Session 2
  SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE NOWAIT SKIP LOCKED;

SELECT *
FROM deadlock
FOR UPDATE NOWAIT SKIP LOCKED;
 
Lock Demo
Blocking Session SELECT <column_names>
FROM <table_name>
FOR UPDATE NOWAIT SKIP LOCKED;
conn uwclass/uwclass

LOCK TABLE servers
IN exclusive mode;
   
  conn uwclass/uwclass

UPDATE servers
SET latitude = 1;
 
    conn / as sysdba

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (
  SELECT id1, id2, type
  FROM V$LOCK
  WHERE request>0)
ORDER BY id1, request;
rollback;    
    SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (
  SELECT id1, id2, type
  FROM V$LOCK
  WHERE request>0)
ORDER BY id1, request;
  rollback;  
Proper way to think about locking files for update in an application set serveroutput on

DECLARE
 x INTEGER;
BEGIN
  SELECT id
  INTO x
  FROM deadlock
  WHERE id = 2
  FOR UPDATE WAIT 2; -- wait up to two seconds for the resource to be released

  UPDATE deadlock
  SET fld = 'X'
  WHERE id = 2;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('The resource is already locked');  -- for demo purposes only
END;
/

Related Topics
Delete Statements
Locks
Merge Statements
Update Statements

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved