CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Deadlocks
Version 11.1
 
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 DISTINCT sid FROM gv$mystat;

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
Note #1: This is no longer an undocumented feature, as of 11g R1.

Oracle Oracle 11.2 Update: The syntax "NOWAIT SKIP LOCKED" is not accepted in Oracle 11.2. The "NOWAIT" keyword and "SKIP LOCKED" keyword have become mutually exclusive. Remove the "NOWAIT" if you want to use "SKIP LOCKED".

By default, the transaction waits until the requested row lock is acquired. If you are not willing to wait to acquire the row lock, use either the NOWAIT clause of the LOCK TABLE statement (see Choosing a Locking Strategy) or the SKIP LOCKED clause of the SELECT FOR UPDATE statement.

If you can lock some of the requested rows, but not all of them, the SKIP LOCKED option skips the rows that you cannot lock and locks the rows that you can lock.

SELECT <column_names>
FROM <table_name>
FOR UPDATE NOWAIT SKIP LOCKED;

Oracle versions prior to 11.2 SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE NOWAIT SKIP LOCKED;

SELECT *
FROM deadlock
FOR UPDATE NOWAIT SKIP LOCKED;
  Oracle versions 11.2 and greater SELECT *
FROM deadlock
WHERE id IN (1, 2)
FOR UPDATE NOWAIT;

SELECT *
FROM deadlock
FOR UPDATE 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;  
 
Related Topics
Locks
Update
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [73 users online]    © 2010 psoug.org