Oracle 12c Deadlocks

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;
/