January 7, 2010
Is it possible to trigger a deadlock on Oracle Database 11.1.0.6, 11.1.0.7, and 11.2.0.1 when the exact same procedure does not trigger a deadlock on Oracle Database 10.2.0.4? Trace files containing deadlocks always contain the following advice:
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.
In short, the above states that deadlocks are programming and/or end-user errors.
A couple of months after I generated a test case like the one below, I read an interesting blog article by Mark Bobak, a fellow OakTable Network member and frequent contributor to the Oracle OTN forums and Oracle-L list. I then started to wonder if the test case I put together applied to Mark’s article (clarification: my test case was built roughly a year after Mark’s article was written, I only recently found Mark’s article).
I have a few favorite Oracle books, and I thought that I would quote from two of those books:
Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions
“So, when do you not need to index a foreign key? The answer is, in general, when the following conditions are met:
- You do not delete from the parent table.
- You do not update the parent table’s unique/primary key value (watch for unintended updates to the primary key by tools!).
- You do not join from the parent to the child (like DEPT to EMP).”
Page 302 of the book “Expert One-On-One” and the reprint Expert Oracle, Signature Edition state essentially the same thing as the above book.
—
Oracle Wait Interface: a Practical Guide to Performance Diagnostics & Tuning
“Unindexed foreign keys are no longer an issue starting in Oracle9i Database.”
Interesting…
I have not read the following book, but found it through a Google search. This book seems to take the middle ground:
OCA Oracle Database 11g: Administration I Exam Guide
“However, you should always create indexes on the foreign key columns within the child table for performance reasons: a DELETE on the parent table will be much faster if Oracle can use an index to determine whether there are any rows in the child table referencing the row that is being deleted.”
OK, combining the three quotes – in general, there are only 3 cases when foreign key columns do not need to be indexed, as of Oracle 9i it is no longer necessary to index foreign key columns, but in Oracle 11g creating an index on a foreign key column will speed up deletes on the parent table.
Let’s keep the above in mind while we trigger a deadlock in Oracle 11.1.0.7. Refer back to the articles on Enqueues and Deadlocks to see if you are able to determine what happens in the test case (or more accurately WHY it happens) (clarification: this test case simulates a “screen painter” type program that updates every column in a row based on the fields on screen – the same behavior should be present if the primary key value in the parent table is updated to the same value, rather than a different value – it also relies on the fact that a row that is inserted by one session but not committed is not visible to another session until the first session commits).
First, we need a couple test tables:
CREATE TABLE T1( C1 NUMBER(10) PRIMARY KEY); INSERT INTO T1 SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=100000; 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 SELECT ROWNUM, ROWNUM FROM DUAL CONNECT BY LEVEL<=100000; COMMIT; GRANT ALL ON T1 TO PUBLIC; GRANT ALL ON T2 TO PUBLIC; CREATE PUBLIC SYNONYM T1 FOR T1; CREATE PUBLIC SYNONYM T2 FOR T2;
OK, we have two tables with a foreign key relationship, and no index on the foreign key column. Now, we need four sessions:
- Session 1 connected as TESTUSER (the owner of the tables)
- Session 2 connected as USER2
- Session 3 connected as USER3
- Session 4 connected as SYS (only to query the various performance views)
Session 1:
INSERT INTO T1 VALUES(100010); INSERT INTO T2 VALUES(100010,100010);
Session 2:
INSERT INTO T1 VALUES(100020); INSERT INTO T2 VALUES(100020,100020); DELETE FROM T2 WHERE C1=50; UPDATE T1 SET C1=100030 WHERE C1=50;{session 2 hangs}
Session 3:
INSERT INTO T1 VALUES(100020);{session 3 hangs}
Session 4:
SELECT /*+ ORDERED */
S.SID,
S.USERNAME,
S.PROGRAM,
S.STATUS,
SW.EVENT,
SW.WAIT_TIME WT,
SW.STATE,
SW.SECONDS_IN_WAIT S_I_W,
S.SQL_ID,
S.SQL_ADDRESS,
S.SQL_HASH_VALUE,
S.SQL_CHILD_NUMBER,
S.ROW_WAIT_OBJ# OBJ#,
S.ROW_WAIT_FILE# FILE#,
S.ROW_WAIT_BLOCK# BLOCK#,
S.ROW_WAIT_ROW# ROW#,
SW.P1,
SW.P2,
SW.P3
FROM
V$SESSION_WAIT SW,
V$SESSION S
WHERE
S.USERNAME IS NOT NULL
AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*Net%'
AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');
SID USERNAME PROGRAM STATUS EVENT WT STATE S_I_W SQL_ID SQL_ADDR SQL_HV CN OBJ# FILE# BLOCK# ROW# P1 P2 P3
--- -------- ----------- ------ -------------------- -- ------- ----- ------------- -------- ---------- -- ---- ----- ------ ---- ---------- ----- --
307 USER2 sqlplus.exe ACTIVE enq: TM - contention 0 WAITING 422 4rtg0hv0atfkx 224E2B7C 3232545373 0 -1 0 0 0 1414332421 82913 0
314 USER3 sqlplus.exe ACTIVE enq: TM - contention 0 WAITING 407 cv338j6z2530g 224DAE38 3189935119 0 -1 0 0 0 1414332419 82913 0
SELECT S.SID, S.USERNAME, S.PROGRAM, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE, S.SQL_CHILD_NUMBER CN, S.ROW_WAIT_OBJ#, S.ROW_WAIT_FILE#, S.ROW_WAIT_BLOCK#, S.ROW_WAIT_ROW#, L.LMODE, L.REQUEST, L.ID1, L.ID2, L.TYPE, L.BLOCK FROM V$LOCK L, V$SESSION S WHERE (L.ID1, L.ID2, L.TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0) AND L.SID=S.SID; SID USERNAME PROGRAM SQL_ID SQL_ADDR SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST ID1 ID2 TY BLOCK --- -------- ----------- ------------- -------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ----- --- -- ----- 307 USER2 sqlplus.exe 4rtg0hv0atfkx 224E2B7C 3232545373 0 -1 0 0 0 3 5 82913 0 TM 1 314 USER3 sqlplus.exe cv338j6z2530g 224DAE38 3189935119 0 -1 0 0 0 0 3 82913 0 TM 0 320 TESTUSER sqlplus.exe 0vbusv12hnbk6 22480E10 1158295110 0 12517 1 29656 0 3 0 82913 0 TM 1
Session 1:
COMMIT;
Session 3 shows the following:
INSERT INTO T1 VALUES(100020) * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource
Session 2 shows the following:
1 row updated.
The trace file contains the following deadlock information:
[Transaction Deadlock] 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 TM-000143e1-00000000 21 314 SX 23 307 SX SSX TX-0009000d-000030d3 23 307 X 21 314 S session 314: DID 0001-0015-00000006 session 307: DID 0001-0017-00000001 session 307: DID 0001-0017-00000001 session 314: DID 0001-0015-00000006 Rows waited on: Session 314: no row Session 307: obj - rowid = 000143E0 - AAAUPgAAFAAFDpsAAA (dictionary objn - 82912, file - 5, block - 1325676, slot - 0) ----- Information for the OTHER waiting sessions ----- Session 307: sid: 307 ser: 2 audsid: 1210519 user: 186/USER2 flags: 0x100045 pid: 23 O/S info: user: SYSTEM, term: TESTBOX, ospid: 940 image: ORACLE.EXE (SHAD) client details: O/S info: user: TEST, term: TESTBOX, ospid: 1616:1264 machine: TESTBOX program: sqlplus.exe application name: SQL*Plus, hash value=3669949024 current SQL: UPDATE T1 SET C1=100030 WHERE C1=50
—
Interesting deadlock. Now, let’s try the same test with Oracle 10.2.0.4:
The table definitions:
CREATE TABLE T1( C1 NUMBER(10) PRIMARY KEY); INSERT INTO T1 SELECT ROWNUM FROM DUAL CONNECT BY LEVEL<=100000; 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 SELECT ROWNUM, ROWNUM FROM DUAL CONNECT BY LEVEL<=100000; COMMIT; GRANT ALL ON T1 TO PUBLIC; GRANT ALL ON T2 TO PUBLIC; CREATE PUBLIC SYNONYM T1 FOR T1; CREATE PUBLIC SYNONYM T2 FOR T2;
Now for the test:
Session 1:
INSERT INTO T1 VALUES(100010); INSERT INTO T2 VALUES(100010,100010);
Session 2:
INSERT INTO T1 VALUES(100020); INSERT INTO T2 VALUES(100020,100020); DELETE FROM T2 WHERE C1=50; UPDATE T1 SET C1=100030 WHERE C1=50;{session 2 hangs}
Session 3:
INSERT INTO T1 VALUES(100020);{session 3 hangs}
Session 4:
SELECT /*+ ORDERED */
S.SID,
S.USERNAME,
S.PROGRAM,
S.STATUS,
SW.EVENT,
SW.WAIT_TIME WT,
SW.STATE,
SW.SECONDS_IN_WAIT S_I_W,
S.SQL_ID,
S.SQL_ADDRESS,
S.SQL_HASH_VALUE,
S.SQL_CHILD_NUMBER,
S.ROW_WAIT_OBJ# OBJ#,
S.ROW_WAIT_FILE# FILE#,
S.ROW_WAIT_BLOCK# BLOCK#,
S.ROW_WAIT_ROW# ROW#,
SW.P1,
SW.P2,
SW.P3
FROM
V$SESSION_WAIT SW,
V$SESSION S
WHERE
S.USERNAME IS NOT NULL
AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*Net%'
AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');
SID USERNAME PROGRAM STATUS EVENT WT STATE S_I_W SQL_ID SQL_ADDR SQL_HV CN OBJ# FILE# BLOCK# ROW# P1 P2 P3
--- -------- ----------- ------ -------------------- -- ------- ----- ------------- -------- ---------- -- ---- ----- ------ ---- ---------- ----- --
204 USER2 sqlplus.exe ACTIVE enq: TM - contention 0 WAITING 213 4rtg0hv0atfkx 2AF83608 3232545373 0 -1 0 0 0 1414332421 16472 0
217 USER3 sqlplus.exe ACTIVE enq: TM - contention 0 WAITING 201 cv338j6z2530g 2AF85A5C 3189935119 0 -1 0 0 0 1414332418 16472 0
SELECT S.SID, S.USERNAME, S.PROGRAM, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE, S.SQL_CHILD_NUMBER CN, S.ROW_WAIT_OBJ#, S.ROW_WAIT_FILE#, S.ROW_WAIT_BLOCK#, S.ROW_WAIT_ROW#, L.LMODE, L.REQUEST, L.ID1, L.ID2, L.TYPE, L.BLOCK FROM V$LOCK L, V$SESSION S WHERE (L.ID1, L.ID2, L.TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0) AND L.SID=S.SID; SID USERNAME PROGRAM SQL_ID SQL_ADDR SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST ID1 ID2 TY BLOCK --- -------- ----------- ------------- -------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ----- --- -- ----- 213 TESTUSER sqlplus.exe 0vbusv12hnbk6 2AF78CC4 1158295110 0 0 2 799 0 3 0 16472 0 TM 1 204 USER2 sqlplus.exe 4rtg0hv0atfkx 2AF83608 3232545373 0 -1 0 0 0 3 5 16472 0 TM 0 217 USER3 sqlplus.exe cv338j6z2530g 2AF85A5C 3189935119 0 -1 0 0 0 0 2 16472 0 TM 0
Session 1:
COMMIT;
Session 3:
{session 3 remains hung}
Session 2:
1 row updated.
Session 4:
SELECT /*+ ORDERED */
S.SID,
S.USERNAME,
S.PROGRAM,
S.STATUS,
SW.EVENT,
SW.WAIT_TIME WT,
SW.STATE,
SW.SECONDS_IN_WAIT S_I_W,
S.SQL_ID,
S.SQL_ADDRESS,
S.SQL_HASH_VALUE,
S.SQL_CHILD_NUMBER,
S.ROW_WAIT_OBJ# OBJ#,
S.ROW_WAIT_FILE# FILE#,
S.ROW_WAIT_BLOCK# BLOCK#,
S.ROW_WAIT_ROW# ROW#,
SW.P1,
SW.P2,
SW.P3
FROM
V$SESSION_WAIT SW,
V$SESSION S
WHERE
S.USERNAME IS NOT NULL
AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*Net%'
AND SW.EVENT NOT IN ('Streams AQ: waiting for messages in the queue', 'wait for unread message on broadcast channel');
SID USERNAME PROGRAM STATUS EVENT WT STATE S_I_W SQL_ID SQL_ADDR SQL_HV CN OBJ# FILE# BLOCK# ROW# P1 P2 P3
--- -------- ----------- ------ ----------------------------- -- ------- ----- ------------- -------- ---------- -- ---- ----- ------ ---- ---------- ------ ----
217 USER3 sqlplus.exe ACTIVE enq: TX - row lock contention 0 WAITING 380 cv338j6z2530g 2AF85A5C 3189935119 0 -1 0 0 0 1415053316 458788 1297
SELECT S.SID, S.USERNAME, S.PROGRAM, S.SQL_ID, S.SQL_ADDRESS, S.SQL_HASH_VALUE, S.SQL_CHILD_NUMBER CN, S.ROW_WAIT_OBJ#, S.ROW_WAIT_FILE#, S.ROW_WAIT_BLOCK#, S.ROW_WAIT_ROW#, L.LMODE, L.REQUEST, L.ID1, L.ID2, L.TYPE, L.BLOCK FROM V$LOCK L, V$SESSION S WHERE (L.ID1, L.ID2, L.TYPE) IN (SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0) AND L.SID=S.SID; SID USERNAME PROGRAM SQL_ID SQL_ADDR SQL_HASH_VALUE CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST ID1 ID2 TY BLOCK --- -------- ----------- ------------- -------- -------------- -- ------------- -------------- --------------- ------------- ----- ------- ------ ---- -- ----- 217 USER3 sqlplus.exe cv338j6z2530g 2AF85A5C 3189935119 0 -1 0 0 0 0 4 458788 1297 TX 0 204 USER2 sqlplus.exe 00 0 -1 0 0 0 6 0 458788 1297 TX 1
—
So, why did Oracle 11.1.0.7 deadlock while 10.2.0.4 did not deadlock (11.1.0.6 and 11.2.0.1 will also deadlock)?
Charles Hooper's Oracle Notes 
Recent Comments