ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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 <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;
/
