Oracle Enqueue Rules Leading to Deadlock Changes Again in 12.1?

1 08 2013

August 1, 2013

Four years ago while co-writing two chapters for an Oracle Database book, an interesting test case was put together to demonstrate how enqueues are handled when multiple sessions are competing for some of the same database resources – the test case was intended to demonstrate the neat, orderly, and predictable process implemented by Oracle Database.  Oracle Database 10.2.0.1 through 10.2.0.4 behaved essentially the same when executing the test case script, while Oracle Database 11.1.0.6, 11.1.0.7, and 11.2.0.1 behaved differently when executing the test case script (11.2.0.2 and 11.2.0.3 behave just like 11.2.0.1 when executing the script).

Oracle Database 11.1.0.x and 11.2.0.x terminated the test case early with a deadlock error.  Jonathan Lewis offered an exceptional explanation of what happened with my test case script in his Lock Horror article.  He also mentioned a couple of interesting notes in comments attached to my article, one of which stated that Oracle Database 9.2.0.5 (or 9.2.0.4) had also introduced a change in enqueue rules related to foreign keys.  Richard Foote put together a related article that explained the changes in locking mode with foreign keys in his Oracle11g: New Locking Modes When Policing FK Constraints article.  Trying not to forget related articles (see the note at the end of this blog article), Tom Kyte also authored a blog article titled Something I recently unlearned… that is somewhat related to the test case script that appeared in the Expert Oracle Practices book.  So, the rules have changed, deadlocks should now be expected when executing the test case script.

I just started experimenting with Oracle Database 12.1.0.1.  My very first experiment with the new database version was to test the behavior of the enqueue script to verify that it results in a deadlock on Oracle Database 12.1.0.1.  Consistency is one of the key factors in a database application – if an application executes:

SELECT 1 + 1 FROM DUAL;

a developer should be reasonably certain that Oracle Database will either return the value 2 or the binary number 10 when the above SQL statement is executed, regardless of whether Oracle Database 6.x or Oracle Database 12.x is used by an application written by the developer.  The same developer should have a reasonable expectation that his application, if it is compatible with Oracle Database 9.0.1, should work pretty much the same way on Oracle Database 12.1.0.1.  But, what happens when Oracle Corp. fixes bugs in Oracle Database?

Consistency is important, but it is also important to recognize that behavior may change consistently.

Let’s reintroduce the test case script from my earlier article (which also appeared in the Expert Oracle Practices book).  Executing this script requires four sessions, each of which is logged in as a different user (to minimize the difficulty associated with deciphering the results of the SQL statements):

  • 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)

First, we need a couple of test tables with a declared foreign key relationship, without an index on the foreign key column.  In Session 1:

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;

In Session 1, insert a row into the parent and child tables without issuing a commit:

INSERT INTO T1 VALUES(100010);
INSERT INTO T2 VALUES(100010,100010);

In Session 2, insert a row into the parent and child tables, delete a row in the child table so that the primary key column value of the associated row in the parent table may be modified, and modify the parent row’s primary key column value:

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 is hung)

Session 3 cannot see that a row was inserted by the now hung session 2, so session 3 attempts to insert a row with the same primary key value.  In Session 3:

INSERT INTO T1 VALUES(100020);

(Session 3 is hung)

The enqueues at this point on Oracle Database 10.2.0.4 differs from those found on Oracle Database 11.1.0.6, and (for consistency) those enqueues differ from what are found on Oracle Database 12.1.0.1.

In Session 4, let’s take a look at the enqueues (note that the join to the V$SESSION_WAIT performance view is unnecessary starting with Oracle Database 10.1.0.x, however I left that join in place to allow easy adaptation of the SQL statement so that it may work with older Oracle Database versions).

SET LINESIZE 165
SET PAGESIZE 1000
SET TRIMSPOOL ON
COLUMN SID FORMAT 9999
COLUMN PROGRAM FORMAT A11
COLUMN USERNAME FORMAT A8
COLUMN LMODE FORMAT 99
COLUMN REQUEST FORMAT 99
COLUMN CN FORMAT 99
COLUMN EVENT FORMAT A30
COLUMN STATE FORMAT A8
COLUMN S_I_W FORMAT 9999
COLUMN WT FORMAT 9999
COLUMN OBJ# FORMAT 999999
COLUMN FILE# FORMAT 999
COLUMN BLOCK# FORMAT 9999999
COLUMN P2 FORMAT 9999999
COLUMN P3 FORMAT 999999

SELECT
  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_CHILD_NUMBER CN,
  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         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  184 USER2    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING     89 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    91943       0
  243 USER3    sqlplus.exe ACTIVE   enq: TX - row lock contention      0 WAITING     49 cv338j6z2530g   0      -1     0        0    0 1415053316    65548    1531

For comparison, here is the output of the same SQL statement on 11.1.0.7 from the earlier blog article:

__SID USERNAME PROGRAM     STATUS   EVENT                             WT STATE    S_I_W SQL_ID         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  307 USER2    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING    422 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    82913       0
  314 USER3    sqlplus.exe ACTIVE   enq: TM - contention               0 WAITING    407 cv338j6z2530g   0      -1     0        0    0 1414332419    82913       0

For comparison, here is the output of the same SQL statement on 10.2.0.4 from the earlier blog article:

__SID USERNAME PROGRAM     STATUS   EVENT                             WT STATE    S_I_W SQL_ID         CN    OBJ# FILE#   BLOCK# ROW#         P1       P2      P3
----- -------- ----------- -------- ------------------------------ ----- -------- ----- ------------- --- ------- ----- -------- ---- ---------- -------- -------
  204 USER2    sqlplus.exe ACTIVE enq: TM - contention                 0 WAITING    213 4rtg0hv0atfkx   0      -1     0        0    0 1414332421    16472       0
  217 USER3    sqlplus.exe ACTIVE enq: TM - contention                 0 WAITING    201 cv338j6z2530g   0      -1     0        0    0 1414332418    16472       0

… USER3 on 12.1.0.1 seems to not be waiting on a table level (TM) enqueue.

In Session 4, let’s try the second SQL statement to examine the lock modes:

SELECT
  S.SID,
  S.USERNAME,
  S.PROGRAM,
  S.SQL_ID,
  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         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  127 TESTUSER sqlplus.exe                              -1              0               0             0     3       0      91943          0 TM        1
  184 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      91943          0 TM        1
  184 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     6       0      65548       1531 TX        1
  243 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     2       0      91943          0 TM        0
  243 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       4      65548       1531 TX        0

For comparison, here is the output of the same SQL statement on 11.1.0.7 from the earlier blog article:

__SID USERNAME PROGRAM     SQL_ID         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  320 TESTUSER sqlplus.exe 0vbusv12hnbk6   0         12517              1           29656             0     3       0      82913          0 TM        1
  307 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      82913          0 TM        1
  314 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       3      82913          0 TM        0

For comparison, here is the output of the same SQL statement on 10.2.0.4 from the earlier blog article:

__SID USERNAME PROGRAM     SQL_ID         CN ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LMODE REQUEST        ID1        ID2 TY    BLOCK
----- -------- ----------- ------------- --- ------------- -------------- --------------- ------------- ----- ------- ---------- ---------- -- --------
  213 TESTUSER sqlplus.exe 0vbusv12hnbk6   0             0              2             799             0     3       0      16472          0 TM        1
  204 USER2    sqlplus.exe 4rtg0hv0atfkx   0            -1              0               0             0     3       5      16472          0 TM        0
  217 USER3    sqlplus.exe cv338j6z2530g   0            -1              0               0             0     0       2      16472          0 TM        0

So, on Oracle Database 12.1.0.1, USER3 is not waiting on a table level (TM) equeue (it is holding a level 2 TM enqueue, while on 10.2.0.4 that session was trying to acquire a level 2 TM lock, and on 11.1.0.7 that session was trying to acquire a level 3 TM lock), it is instead waiting on a row level (TX – transaction) enqueue that is held in exclusive mode (level 6) by USER2.  Quite honestly, that result is similar to what I was hoping to see four years ago when the test script was put together, because USER3 really is waiting due to a potential primary key collision, if session 2 issues a commit.

Session 2 is hung, so it cannot issue a commit.  So, in Session 1:

COMMIT;

Session 2 is no longer hung, and now shows:

1 row updated.

Session 3, of course, remains hung waiting for Session 2 to either issue a COMMIT or a ROLLBACK to resolve the potential primary key collision.

For comparison, Session 3 on 11.1.0.7 from the earlier blog article showed the following:

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

… and Session 2 showed the following on 11.1.0.7:

1 row updated.

Is it possible that the Session 2 could have displayed a deadlock message, rather than Session 3 showing a deadlock message?  I seem to remember reading that there is a good chance that the session that has been in the enqueue the longest could be selected as the “vicitim” of the deadlock, but I might be incorrectly remembering that rule.  Oracle Database really did not completely resolve the deadlock, nor did it “kill” session 3 (as too many books and blog article state would happen) when the deadlock message appeared in that session’s SQL*Plus session.

For comparison, Oracle Database 10.2.0.4 behaved similar to Oracle Database 12.1.0.1, with Session 2 showing 1 row updated, and Session 3 remaining hung.  The more things change, the more they stay the same?

Thoughts?  Curious about the test cases found on the web pages that I linked to earlier?  Does Oracle Database 12.1.0.1 behave more like 11.2.0.3 or 10.2.0.4 when foreign key columns are not properly indexed?





Faulty Quotes 7 – Deadlock Kills Sessions?

4 01 2012

January 4, 2012

(Back to the Previous Post in the Series)

I thought that I would begin this blog article with a bit of a test case that demonstrates a very simple deadlock between two sessions.

In Session 1 (create the test table with a primary key and insert a single row):

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

INSERT INTO T1 VALUES (1,'1');

In Session 2 (insert a row that does not conflict with session 1 and then insert a row with the same primary key value as the uncommitted row in Session 1):

INSERT INTO T1 VALUES (2,'2');

INSERT INTO T1 VALUES (1,'3');

(Session 2 hangs while waiting for Session 1 to COMMIT or ROLLBACK)

In Session 1 (insert a row with the same primary key value as the uncommitted row in Session 2):

INSERT INTO T1 VALUES (2,'2');

(Session 1 hangs while waiting for Session 2 to COMMIT or ROLLBACK)

In Session 2 (an ORA-00060 is returned, deadlock between the two sessions ends):

INSERT INTO T1 VALUES (1,'3')
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

(Session 1 still hangs while waiting for Session 2 to COMMIT or ROLLBACK the first row that was inserted)

In Session 2 (confirming that the session was not killed/terminated and that the first row inserted still exists – indicates that the current TRANSACTION was not rolled back, just the statement that was involved in the deadlock):

SELECT
  *
FROM
  T1;

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

While recently reading an OTN thread I was reminded of a bit of information that several sources simply miss-state about Oracle deadlocks.  The incorrect information is repeated, seemingly quite frequently, in various books and forum threads.  As luck would have it, several of the books that I have read about Oracle Database also repeat this incorrect information.  For instance, a quote from page 352 of the book “Expert Oracle Database 11g Administration” (a book that I reviewed and gave 4 out of 5 stars, maybe I need to re-evaluate that rating?):

“This is a catch-22 situation, because the stalemate can’t be broken by either session unilaterally. In such circumstances, Oracle steps in, terminates one of the sessions, and rolls back its statement. Oracle quickly recognizes that two sessions are deadlocked and terminates the session that issued the most recent request for a lock. This will release the object locks that the other session is waiting for.”

A second book, “Secrets of the Oracle Database” (another book that I reviewed and gave 4 out of 5 stars, actually it was probably worth about 4.4 stars), states the following on page 57:

“Hence, the ORACLE DBMS detects circular chains pertaining to interdependent locks, signals the error ‘ORA-00060: deadlock detected while waiting for resource’, and rolls back one of the sessions involved in the would-be deadlock.”

A third book, “Oracle SQL Recipes” (another book that I reviewed and gave 4 out of 5 stars), states the following on page 217:

“The immediate solution to the problem requires no user intervention: Oracle detects the deadlock and automatically rolls back one of the transactions, releasing one of the sets of row locks so that the other session’s DML will complete.”

A fourth book, “Oracle Database 11g Performance Tuning Recipes” (another book that I reviewed and gave 2 out of 5 stars), states on page 227:

“While the database can automatically resolve deadlocks between sessions (by killing one of the sessions holding a needed lock)…”

The Google book search found that the above four books were not alone in their statements of deadlocks that do not match the test case results at the start of this blog article.  One of the books found by Google is one that I have not read yet (and probably never will), “Oracle PL/SQL for Dummies”, which states the following on page 298:

“This is a deadlock. Oracle resolves it by raising a deadlock exception (ORA-00060: deadlock detected while waiting for resource) in user 1′s session. This terminates the procedure and allows user 2′s procedure to successfully complete.”

A second book found by the Google search is a bit old, “Oracle 9i DBA 101″, which on page 194 states:

“A deadlock occurs when two or more users wait for data that is locked by each other. Oracle will kill the first server process that detects the deadlock, so you should always look for and fix code that can cause deadlocks.”

There are certainly many other statements in other books regarding deadlocks, both correct and incorrect.  A quick search through the OTN forums found several threads in addition to the one at the start of this blog article that also drifted a bit from an accurate picture of what happens when a deadlock is broken in Oracle Database.

https://forums.oracle.com/forums/thread.jspa?threadID=2196282

“That is the deadlock. Oracle will then immediately choose arbitrarlly a victim session (542 or 548) and kill its process letting the remaining session continuing its work.”

https://forums.oracle.com/forums/thread.jspa?threadID=2152646

“There should be a simple wait, not a deadlock which would killed one of the session.”

https://forums.oracle.com/forums/thread.jspa?threadID=2261367

“If two sessions are actually deadlocked, Oracle should always kill one of the blocked sessions, raising an ORA-00060 error. It sounds like you’re saying this is not the expected behavior.”

https://forums.oracle.com/forums/thread.jspa?threadID=2227420

“The two session are inserting records in the same table with a unique index on it. With the same database version on another server and the same treatment, the deadlocks are correctly handled (one session is killed with ora-00060) and the process can continue.”

https://forums.oracle.com/forums/thread.jspa?threadID=1072665

“In oracle, when session1 and session2 make a deadlock, the oracle will kill one session , but I want know how to let oracle kill session1 and session2 both, is there way to do that?”

https://forums.oracle.com/forums/thread.jspa?threadID=2169563

“NO. Oracle already broke the deadlock by terminating one of these sessions. By the time trace file was closed, the deadlock no longer existed.”

https://forums.oracle.com/forums/thread.jspa?threadID=953308

“Oracle itself resolve the deadlock by killing the session.”

While reading the book “Oracle Core Essential Internals for DBAs and Developers” I almost flagged a sentence in a discussion of deadlocks as potentially containing an error.  That statement is found on page 82 near the end of the third to the last paragraph on the page.  After reading that paragraph about 10 times I decided that the statement is probably specific to TM locks, and probably is correct (the thought of TX locks maintained at the individual block level that contains the table rows kept running through my head the first nine times I read the paragraph).  This section of the book is interesting, as it not only avoids the mistakes mentioned in the above books, but it also highlights an inaccurate statement or two found in the Oracle Database documentation.





Deadlock on Oracle 11g but Not on 10g

7 01 2010

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)?





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)







Follow

Get every new post delivered to your Inbox.

Join 148 other followers