| Demo |
| Deadlocks Demo |
-- session 1
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
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
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; |
-- session 2 |
| |
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; |
| |
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>; |
| |
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
SELECT <column_names>
FROM <table_name>
FOR UPDATE NOWAIT SKIP LOCKED; |
| |
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;
/ |
