| 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; |
|
|
