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.
Be sure to view the full listing of monographs in Morgan's Library
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;
/
