Oracle Deadlocks
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
 
Demos
Deadlocks Demo

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

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@pdbdev

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

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

-- session as SYS
conn sys@pdbdev 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 sys@pdbdev as sysdba

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

conn uwclass/uwclass@pdbdev

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
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@pdbdev

LOCK TABLE servers
IN exclusive mode;
   
  conn uwclass/uwclass@pdbdev

UPDATE servers
SET latitude = 1;
 
    conn sys@pdbdev 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
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved