Deadlock Experimentations

6 12 2009

December 6, 2009

Did you know that it is possible for multiple sessions to incorrectly trigger a deadlock on Oracle 11.1.0.6, 11.1.0.7, and 11.2.0.1 by executing a sequence of commands, when the same sequence of commands does not trigger a deadlock on 10.2.0.4?  See chapter 8 “Understanding Performance Optimization Methods” of the “Expert Oracle Practices: Oracle Database Administration from the Oak Table” book for the test case.

Over the years I have posted several test cases that will intentionally trigger a deadlock to permit others to locate a matching deadlock pattern to help identify a cause of their deadlock.  Below are a couple of those test cases.

http://forums.oracle.com/forums/thread.jspa?threadID=689654

In the demo, the sessions are holding SX (Row exclusive – write access to a compound resource) locks due to a select for update, rather than S (Share – One of more sessions are reading) locks, but with a bit more experimentation, I am sure that it would be possible to create a demo where both the sessions are holding S locks.

The demo:

/* First, the creation of the tables with a foreign key index */
CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);

INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);

COMMIT;

CREATE TABLE T2(
  C1 NUMBER(10) PRIMARY KEY,
  C2 NUMBER(10),
  CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

CREATE INDEX IND_T2_C2 ON T2(C2);

INSERT INTO T2 VALUES (1,1);
INSERT INTO T2 VALUES (2,2);
INSERT INTO T2 VALUES (3,3);
INSERT INTO T2 VALUES (4,4);

COMMIT;

 

/* Test 1 - No problems */
/* SESSION 1 */
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

        C1         C2
---------------------
         1          1

 

/* SESSION 2 */
SELECT
  *
FROM
  T2
WHERE
  C2=2
FOR UPDATE;

        C1         C2
---------------------
         2          2

 

UPDATE
  T1
SET
  C1=2
WHERE
  C1=2;

1 row updated.

 

/* SESSION 1 */
UPDATE
  T1
SET
  C1=1
WHERE
  C1=1;

1 row updated.

ROLLBACK;

 

/* SESSION 2 */
ROLLBACK;

/* Test 2 – What happens when we repeat the test without the foreign key index */

DROP INDEX IND_T2_C2;

/* SESSION 2 */
SELECT
  *
FROM
  T2
WHERE
  C2=2
FOR UPDATE;

        C1         C2
---------------------
         2          2

 

/* SESSION 1 */
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

        C1         C2
---------------------
         1          1

UPDATE
  T1
SET
  C1=1
WHERE
  C1=1;
/* HANGS */

/* SESSION 2 */
UPDATE
  T1
SET
  C1=2
WHERE
  C1=2;
/* HANGS */

 

/* SESSION 1 */
  T1
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

 

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000d63f-00000000        17     208    SX   SSX       18     210    SX   SSX
TM-0000d63f-00000000        18     210    SX   SSX       17     208    SX   SSX
session 208: DID 0001-0011-000000CE session 210: DID 0001-0012-000000D7
session 210: DID 0001-0012-000000D7 session 208: DID 0001-0011-000000CE
Rows waited on:
Session 210: obj - rowid = 000027EB - AAACfrAABAAAG/BAAA
  (dictionary objn - 10219, file - 1, block - 28609, slot - 0)

Your deadlock graph showed that both sessions were trying to obtain a TM lock in mode 5 (SSX), but the other sessions were already holding locks in mode 4 (S), and neither session was able to continue.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?threadID=689654

If the deadlock points to a type TX, then the problem is probably not related to foreign keys indexing problems.

Another test setup to produce a deadlock with lock type TX:

/* The setup (assumes that the tables from the previous test setup still exist) */
DROP TABLE T2;
DROP TABLE T1;
DROP TABLE T3;

CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY, C2 NUMBER(10));

INSERT INTO T1 VALUES(1,NULL);
INSERT INTO T1 VALUES(2,NULL);
INSERT INTO T1 VALUES(3,NULL);
INSERT INTO T1 VALUES(4,NULL);

COMMIT;

CREATE TABLE T2(C1 NUMBER(10) PRIMARY KEY, C2 NUMBER(10));

INSERT INTO T2 VALUES(1,NULL);
INSERT INTO T2 VALUES(2,NULL);
INSERT INTO T2 VALUES(3,NULL);
INSERT INTO T2 VALUES(4,NULL);

COMMIT;

CREATE TABLE T3(TRANSACTION_ID NUMBER(10) PRIMARY KEY);

INSERT INTO T3 VALUES(1);
INSERT INTO T3 VALUES(2);
INSERT INTO T3 VALUES(3);
INSERT INTO T3 VALUES(4);

COMMIT;

/* Now we have two data tables and a third table, which could be interesting if there were 3 sessions involved */

/* Test 3 - session 1 updates a row, waits, session 2 updates 2 rows and hangs */
/* SESSION 1 */
UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;

 

/* SESSION 2 */
UPDATE
  T2
SET
  C2=C1
WHERE
  C1=1;

UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;
/* HANGS */

/* SESSION 1 */
UPDATE
  T2
SET
  C2=C1
WHERE
  C1=1;
/* HANGS */

/* SESSION 2 */
  T1
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010003-0000238f        18     208     X             17     210           X
TX-00090005-00002383        17     210     X             18     208           X
session 208: DID 0001-0012-000000D9 session 210: DID 0001-0011-000000D0
session 210: DID 0001-0011-000000D0 session 208: DID 0001-0012-000000D9
Rows waited on:
Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
  (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
  (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)

 

/* SESSION 2 */
ROLLBACK;

 

/* SESSION 1 */
ROLLBACK;

 
/* Test 4 – Transaction table that does not use a sequence contributes to the problem */

/* SESSION 1 */
UPDATE
  T1
SET
  C2=C1
WHERE
  C1=1;

INSERT INTO
  T3
SELECT
  MAX(TRANSACTION_ID)+1
FROM
  T3;

 

/* SESSION 2 */
UPDATE
  T2
SET
  C2=C1+1
WHERE
  C1=1;

INSERT INTO
  T3
SELECT
  MAX(TRANSACTION_ID)+1
FROM
  T3;
/* HANGS */

 

/* SESSION 1 */
DELETE FROM T2;
/* HANGS */

 

/* SESSION 2 */
  T3
  *
ERROR at line 2:
ORA-00060: deadlock detected while waiting for resource

 

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00060029-00002376        18     208     X             17     210           X
TX-00090016-00002386        17     210     X             18     208           S
session 208: DID 0001-0012-000000D9 session 210: DID 0001-0011-000000D0
session 210: DID 0001-0011-000000D0 session 208: DID 0001-0012-000000D9
Rows waited on:
Session 210: obj - rowid = 0000D644 - AAANZEAAEAAFvr2AAA
  (dictionary objn - 54852, file - 4, block - 1506038, slot - 0)
Session 208: obj - rowid = 0000D642 - AAANZCAAEAAFvrmAAA
  (dictionary objn - 54850, file - 4, block - 1506022, slot - 0)

Test 5 (if setup) might have used 3 sessions, the first session would update a row in T1 and then insert a row into T3. The second session would update a row in T2 and attempt to insert a row into T3 (session 2 would hang). Session 3 would update a different row in T2, and then attempt to insert a row into T3 (session 3 would hang). Session 1 would see that the row originally updated by session 2 was not updated, and attempt to update that row (session 1 would hang). The Deadlock graph?

Deadlocks almost always point to application design problems (or user design problems when the user attempts to start the same batch process twice).

One thing that you do not know is what the two sessions were doing before they executed the insert into T2 statement. The first set of numbers in the deadlock graph is the hex value of an OBJECT_ID. If you execute the following query:

SELECT
  OWNER,
  OBJECT_NAME
FROM
  DBA_OBJECTS
WHERE
  OBJECT_ID=241021;

Does it show the table T2, T1, T3, or some other table?

You might find the following Metalink documents helpful:
Note:15476.1 FAQ about Detecting and Resolving Locking Conflicts
Note:198828.1 Analysing locking issues with LOGMINER
Note:102925.1 Tracing sessions: waiting on an enqueue

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

http://forums.oracle.com/forums/thread.jspa?threadID=867295
The setup that I was trying to describe was one in which the TM lock could not be obtained due to another session already holding a TX lock on the child, something like this:

CREATE TABLE T1(C1 NUMBER(10) PRIMARY KEY);

INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);

COMMIT;

CREATE TABLE T2(
  C1 NUMBER(10) PRIMARY KEY,
  C2 NUMBER(10),
  CONSTRAINT FK_T1_C1 FOREIGN KEY(C2) REFERENCES T1(C1) ENABLE);

INSERT INTO T2 VALUES (1,1);
INSERT INTO T2 VALUES (2,2);
INSERT INTO T2 VALUES (3,3);
INSERT INTO T2 VALUES (4,4);

COMMIT;

In session 1:
SELECT
  *
FROM
  T2
WHERE
  C2=1
FOR UPDATE;

In session 2:
INSERT INTO T1 VALUES(15);

UPDATE
  T1
SET
  C1=15
WHERE
  C1=15;

(Session 2 hangs trying to acquire a TM lock on the child table)

In session 1:
INSERT INTO T1 VALUES(15);

(Session 1 and 2 deadlock)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a0025-000038bf        17     213     X             18     210           S
TM-0000d79a-00000000        18     210    SX             17     213    SS     S
session 213: DID 0001-0011-000003C2 session 210: DID 0001-0012-000002F1
session 210: DID 0001-0012-000002F1 session 213: DID 0001-0011-000003C2
Rows waited on:
Session 210: no row
Session 213: no row

 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In session 1

CREATE TABLE T1(
  MY_ID VARCHAR2(30),
  MY_DATE DATE,
  CUSTOMER_ID VARCHAR2(15),
  PRIMARY KEY (MY_ID));

INSERT INTO T1 VALUES(
  '001',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C001');

INSERT INTO T1 VALUES(
  '002',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C002');

INSERT INTO T1 VALUES(
  '003',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '004',
  TO_DATE('01-JAN-2006','DD-MON-YYYY'),
  'C001');

COMMIT;

INSERT INTO T1 VALUES(
  '005',
  TO_DATE('01-JAN-2007','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '006',
  TO_DATE('01-JAN-2007','DD-MON-YYYY'),
  'C001');

In session 2:

INSERT INTO T1 VALUES(
  '007',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C003');

INSERT INTO T1 VALUES(
  '008',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001');

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='001';

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='002';

UPDATE
  T1
SET
  MY_DATE=MY_DATE+1
WHERE
  MY_ID='003';

INSERT INTO T1 VALUES(
  '005',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001');

(The above will be a primary key violation if session 1 commits, so session 2 hangs)

In session 2’s 10046 trace file:

------------
*** 2008-02-12 10:09:27.984
WAIT #2: nam='enq: TX - row lock contention' ela= 3000118 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1749514958
WAIT #2: nam='enq: TX - row lock contention' ela= 3000116 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1752515272
WAIT #2: nam='enq: TX - row lock contention' ela= 3000157 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1755515569
WAIT #2: nam='enq: TX - row lock contention' ela= 3000084 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1758515803
*** 2008-02-12 10:09:39.983
WAIT #2: nam='enq: TX - row lock contention' ela= 3000079 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1761516009
WAIT #2: nam='enq: TX - row lock contention' ela= 2999987 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1764516178
WAIT #2: nam='enq: TX - row lock contention' ela= 2999994 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1767516313
WAIT #2: nam='enq: TX - row lock contention' ela= 3000015 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1770516482
*** 2008-02-12 10:09:51.983
WAIT #2: nam='enq: TX - row lock contention' ela= 3000084 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1773516699
WAIT #2: nam='enq: TX - row lock contention' ela= 3000063 name|mode=1415053316 usn<<16 | slot=131104 sequence=8831 obj#=-1 tim=1776516940
------------

In session 1:

SELECT
  *
FROM
  T1
FOR UPDATE;

Session 1 hangs

Session 2 immediately displays:

INSERT INTO TESTUSER.T1 VALUES(
                    *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

In session 2’s 10046 trace file:

DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO TESTUSER.T1 VALUES(
  '005',
  TO_DATE('01-FEB-2007','DD-MON-YYYY'),
  'C001')
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0006002d-000023ef        25     201     X             22     204           X
TX-00020020-0000227f        22     204     X             25     201           S
session 201: DID 0001-0019-000001D2 session 204: DID 0001-0016-000000FF
session 204: DID 0001-0016-000000FF session 201: DID 0001-0019-000001D2
Rows waited on:
Session 204: obj - rowid = 0000829B - AAAIKbAAEAAEBROAAA

In session 2:

SELECT
  *
FROM
  T1
WHERE
  ROWID='AAAIKbAAEAAEBROAAA';

MY_ID                          MY_DATE   CUSTOMER_ID
------------------------------ --------- -----------
001                            02-JAN-06 C001

Session 2’s update of the row with MY_ID =’001′ was pinpointed in the deadlock.

In session 1’s 10046 trace file:

PARSING IN CURSOR #2 len=31 dep=0 uid=288 oct=3 lid=288 tim=1776466041 hv=3199333328 ad='46baac58'
SELECT
  *
FROM
  T1
FOR UPDATE
END OF STMT
PARSE #2:c=0,e=93402,p=0,cr=17,cu=0,mis=1,r=0,dep=0,og=1,tim=1776466031
BINDS #2:
WAIT #2: nam='enq: TX - row lock contention' ela= 2997875 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1779470295
WAIT #2: nam='enq: TX - row lock contention' ela= 3000109 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1782470542
WAIT #2: nam='enq: TX - row lock contention' ela= 3000151 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1785470824
*** 2008-02-12 10:10:06.936
WAIT #2: nam='enq: TX - row lock contention' ela= 3000127 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1788471107
WAIT #2: nam='enq: TX - row lock contention' ela= 2999987 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1791471260
WAIT #2: nam='enq: TX - row lock contention' ela= 3000059 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1794471450
WAIT #2: nam='enq: TX - row lock contention' ela= 3000020 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1797471623
*** 2008-02-12 10:10:18.936
WAIT #2: nam='enq: TX - row lock contention' ela= 3000059 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1800471821
WAIT #2: nam='enq: TX - row lock contention' ela= 2999913 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1803471915
WAIT #2: nam='enq: TX - row lock contention' ela= 3000109 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1806472157
WAIT #2: nam='enq: TX - row lock contention' ela= 3000086 name|mode=1415053318 usn<<16 | slot=393261 sequence=9199 obj#=33435 tim=1809472374

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Experimenting with a 4 session deadlock:

Session 1:
CREATE TABLE T1(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T1 UNIQUE);
CREATE TABLE T2(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T2 UNIQUE);
CREATE TABLE T3(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T3 UNIQUE);
CREATE TABLE T4(C1 NUMBER PRIMARY KEY, C2 NUMBER CONSTRAINT CON_T4 UNIQUE);

INSERT INTO T1 VALUES (1,1);
INSERT INTO T2 VALUES (1,1);
INSERT INTO T3 VALUES (1,1);
INSERT INTO T4 VALUES (1,1);

COMMIT;

INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

UPDATE T1 SET C2=3 WHERE C1=1;

Session 2:
INSERT INTO T2 SELECT   2,   MAX(C2)+1 C2 FROM   T2;   UPDATE T2 SET C2=3 WHERE C1=1;  

Session 3:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

UPDATE T3 SET C2=3 WHERE C1=1;

Session 4:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

UPDATE T4 SET C2=3 WHERE C1=1;

Session 1:
INSERT INTO T2
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T2;

(Session 1 hung)

Session 2:
INSERT INTO T3
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T3;

(Session 2 hung)

Session 3:
INSERT INTO T4
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T4;

(Session 3 hung)

Session 4:
INSERT INTO T1
SELECT
  3,
  MAX(C2)+1 C2
FROM
  T1;

(Session 4 hung, triggers a deadlock in session 3)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003000f-00002d44        21     144     X             20     145           S
TX-0004000e-0000218e        20     145     X             19     146           S
TX-00070013-000021f4        19     146     X             22     143           S
TX-000a0021-000021f1        22     143     X             21     144           S

session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003
session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007

Rows waited on:
  Session 144: no row
  Session 145: no row
  Session 146: no row
  Session 143: no row

(Rollback all sessions)

 

Session 1:
UPDATE T1 SET C2=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C2=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C2=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C2=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C2=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C2=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C2=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C2=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 1)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00010012-000021f2        19     146     X             22     143           X
TX-00030010-00002d4b        22     143     X             21     144           X
TX-0009000a-00002c69        21     144     X             20     145           X
TX-00060003-00002c5d        20     145     X             19     146           X

session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007
session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003

Rows waited on:
  Session 146: obj - rowid = 00015185 - AAAVGFAAEAAFps4AAA
  (dictionary objn - 86405, file - 4, block - 1481528, slot - 0)
  Session 143: obj - rowid = 00015182 - AAAVGCAAEAAFpsgAAA
  (dictionary objn - 86402, file - 4, block - 1481504, slot - 0)
  Session 144: obj - rowid = 0001518B - AAAVGLAAEAAFptoAAA
  (dictionary objn - 86411, file - 4, block - 1481576, slot - 0)
  Session 145: obj - rowid = 00015188 - AAAVGIAAEAAFptQAAA
  (dictionary objn - 86408, file - 4, block - 1481552, slot - 0)

 

Session 1:
INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

Session 2:
INSERT INTO T2
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T2;

Session 3:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

Session 4:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

Session 1:
INSERT INTO T2
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T2;

(Session 1 hung)

Session 2:
INSERT INTO T3
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T3;

(Session 2 hung)

Session 3:
INSERT INTO T4
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T4;

(Session 3 hung)

Session 4:
INSERT INTO T1
SELECT
  2,
  MAX(C2)+1 C2
FROM
  T1;

(Session 4 hung, triggers a deadlock in session 2)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0004001d-000008c5        24     144     X             22     143           S
TX-0006001c-00000b8e        22     143     X             25     152           S
TX-00080003-00000b7c        25     152     X             23     158           S
TX-00090027-00000b7c        23     158     X             24     144           S
session 144: DID 0001-0018-00000002 session 143: DID 0001-0016-00000002
session 143: DID 0001-0016-00000002 session 152: DID 0001-0019-00000002
session 152: DID 0001-0019-00000002 session 158: DID 0001-0017-00000006
session 158: DID 0001-0017-00000006 session 144: DID 0001-0018-00000002
Rows waited on:
Session 143: no row
Session 152: no row
Session 158: obj - rowid = 0000BD35 - AAAL01AAEAAGGSOAAA
  (dictionary objn - 48437, file - 4, block - 1598606, slot - 0)
Session 144: no row

(Rollback all sessions)

 

Session 1:
UPDATE T1 SET C1=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C1=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C1=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C1=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C1=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C1=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C1=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C1=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 3)

                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00070028-00000871        23     158     X             24     144           X
TX-0006000c-00000b8f        24     144     X             22     143           X
TX-0005002d-00000b88        22     143     X             25     152           X
TX-00090004-00000b7d        25     152     X             23     158           X
session 158: DID 0001-0017-00000006 session 144: DID 0001-0018-00000002
session 144: DID 0001-0018-00000002 session 143: DID 0001-0016-00000002
session 143: DID 0001-0016-00000002 session 152: DID 0001-0019-00000002
session 152: DID 0001-0019-00000002 session 158: DID 0001-0017-00000006
Rows waited on:
Session 144: obj - rowid = 0000BD32 - AAAL0yAAEAAFhH2AAA
  (dictionary objn - 48434, file - 4, block - 1446390, slot - 0)
Session 143: obj - rowid = 0000BD2F - AAAL0vAAEAAFhHeAAA
  (dictionary objn - 48431, file - 4, block - 1446366, slot - 0)
Session 152: obj - rowid = 0000BD2C - AAAL0sAAEAAFOjOAAA
  (dictionary objn - 48428, file - 4, block - 1370318, slot - 0)
Session 158: obj - rowid = 0000BD35 - AAAL01AAEAAGGSOAAA
  (dictionary objn - 48437, file - 4, block - 1598606, slot - 0)

(Rollback all sessions)

 

Session 1:
ALTER TABLE T1 ADD (C3 NUMBER);
ALTER TABLE T2 ADD (C3 NUMBER);
ALTER TABLE T3 ADD (C3 NUMBER);
ALTER TABLE T4 ADD (C3 NUMBER);

Session 1:
UPDATE T1 SET C3=3 WHERE C1=1;

Session 2:
UPDATE T2 SET C3=3 WHERE C1=1;

Session 3:
UPDATE T3 SET C3=3 WHERE C1=1;

Session 4:
UPDATE T4 SET C3=3 WHERE C1=1;

Session 1:
UPDATE T2 SET C3=3 WHERE C1=1;

(Session 1 hung)

Session 2:
UPDATE T3 SET C3=3 WHERE C1=1;

(Session 2 hung)

Session 3:
UPDATE T4 SET C3=3 WHERE C1=1;

(Session 3 hung)

Session 4:
UPDATE T1 SET C3=3 WHERE C1=1;

(Session 4 hung, triggers a deadlock in session 1)

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090005-00002c7c        19     146     X             22     143           X
TX-0005001c-00002cb3        22     143     X             21     144           X
TX-0007000f-0000223a        21     144     X             20     145           X
TX-00080020-00002d01        20     145     X             19     146           X

session 146: DID 0001-0013-00000003 session 143: DID 0001-0016-00000001
session 143: DID 0001-0016-00000001 session 144: DID 0001-0015-00000007
session 144: DID 0001-0015-00000007 session 145: DID 0001-0014-00000003
session 145: DID 0001-0014-00000003 session 146: DID 0001-0013-00000003

Rows waited on:
  Session 146: obj - rowid = 00015185 - AAAVGFAAEAAFps4AAA
  (dictionary objn - 86405, file - 4, block - 1481528, slot - 0)
  Session 143: obj - rowid = 00015182 - AAAVGCAAEAAFpsgAAA
  (dictionary objn - 86402, file - 4, block - 1481504, slot - 0)
  Session 144: obj - rowid = 0001518B - AAAVGLAAEAAFptoAAA
  (dictionary objn - 86411, file - 4, block - 1481576, slot - 0)
  Session 145: obj - rowid = 00015188 - AAAVGIAAEAAFptQAAA
  (dictionary objn - 86408, file - 4, block - 1481552, slot - 0)

« »


Actions

Information

  • Date : December 6, 2009
  • Categories : Deadlock

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s


%d bloggers like this: