Deadlocks


What is a deadlock and how does oracle handle it?
A deadlock is a condition where two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock, thus releasing one set of data locked by that statement. Statement rolled back is usually the one which detects the deadlock.
Deadlocks are mostly caused by explicit locking because oracle does not do lock escalation and does not use read locks. Multi table deadlocks can be avoided by locking the tables in same order in all the applications, thus precluding a deadlock.

Resolving Oracle deadlocks

There are several remedies for resolving aborted tasks from deadlocks:
  • Tune the application - Single-threading related updates and other application changes can often remove deadlocks.  Re-scheduling batch update jobs to low-update times an also help.
     
  • Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes (adding slots to the ITL) can relieve deadlocks.
     
  • Use smaller blocks with less data - Since the deadlock contention is at the block-level, consider moving these tables and indexes to a super-small blocksize (create a db2k_cache_size), and using a high PCTFREE to space-out the data over MORE blocks.
Demo:
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
  WHERE sid=a.sid) blocker,
  a.sid, ' is blocking ', (
  SELECT username
  FROM gv
  WHERE sid=b.sid) blockee,
  b.sid
FROM gv a, gv 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;

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)