Oracle Deadlocks
Version 19.3

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
 
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 18c
What's New In 19c

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