The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1
and version 18.0 is going to be available soon.
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 and beyond.
Have you viewed the Library's main Table of Contents? If not ... [Click Here]
... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Demo
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 / 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
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 <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
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;
/
