Oracle Scratchpad

May 9, 2016

RI Locks

Filed under: deadlocks,Indexing,Locks,Oracle,trace files,Troubleshooting — Jonathan Lewis @ 12:24 pm BST May 9,2016

RI = Referential Integrity: also known informally as parent/child integrity, and primary (or unique) key/foreign key checking.

I’m on a bit of a roll with things that I must have explained dozens or even hundreds of times in different environments without writing them up on my blog, so here’s a blog note I could have used to answer a question that came up over the weeked on the Oracle database forum.

What happens in the following scenario:

create table parent (
        id        number(8,0),
        constraint par_pk primary key(id)

create table child  (
        id_p      number(8,0) not null references parent,
        id_c      number(8,0) not null,
        constraint child_pk primary key(id_p, id_c)

--      session 1

insert into parent values(1);

--      session 2

insert into child values(1,1);

Since the parent row corresponding to the child row doesn’t seem to exist as far as session 2 is concerned you might expect session 2 to respond immediately with an error message like:

ERROR at line 1:
ORA-02291: integrity constraint (TEST_USER.SYS_C0017926) violated - parent key not found

In fact, although the end-user session is not allowed to see the uncommitted parent row, the user’s shadow process can see the uncommitted row and will wait until session 1 commits or rolls back – so if you examine v$lock for the current locks for the two sessions you’d see something like this:

  1  select  sid, type, id1, id2, lmode, request, ctime, block
  2  from    V$lock
  3  where   sid in (select sid from V$session where username = 'TEST_USER')
  4  and     type != 'AE'
  5  order by
  6*         sid, type desc
  7  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         3 TX     327709      12584          6          0        283          1
           TM     143734          0          2          0        283          0
           TM     143732          0          3          0        283          0

       250 TX     589829      12877          6          0        240          0
           TX     327709      12584          0          4        240          0
           TM     143734          0          3          0        240          0
           TM     143732          0          3          0        240          0

7 rows selected.

In the above, SID 250 is session 2: it’s holding a transaction lock (TX) in mode 6 (exclusive) because it has acquired an undo segment and has generated some undo, it’s also waiting for a transaction lock in mode 4 (share) and – checking id1 and id2 – we can see that the transaction table entry it’s waiting for is held by SID 3 (session 1)  in mode 6 (and we also note that the lock held by SID 3 is marked as a blocker).

If SID 3 commits (thus releasing the transaction lock) SID 250 will continue processing the insert; if SID 3 rolls back SID 250 will raise error ORA-02291 and roll back its insert statement. (Note: if this were a multi-statement transaction it would only be the insert into child that would be rolled back; that’s another one of those details that is important but often isn’t stated explicitly, leaving people believing that the entire transaction would be rolled back.)

Updates and deletes can produce the same effects. Imagine that we have just created the two tables, and then run the following:

--      session 1

insert into parent values(1);
delete from parent where id = 1;

--      session 2

insert into child values(1,1);

Again session 2 will wait for session 1 to commit or roll back. In this case if session 1 commits session 2 will raise Oracle error ORA-02291, if session 1 rolls back session 2 will continue with the insert.


Whenever you can demonstrate a way of producing a wait chain you can also manage to produce a deadlock. Consider the following (starting, again, from empty tables);

-- (1) session 1
insert into parent values(1);

-- (2) session 2
insert into parent values(2);

-- (3) session 1
insert into child values(2,2);

-- (4)session 2
insert into child values(1,1);

Session 1 will start waiting for session 2 to commit (or rollback) at step 3, then session 2 will start to wait for session 1 at step 4 – with the result that session 1 will recognise the deadlock after about three seconds and rollback its last statement, raising exception ORA-00060 and dumping a trace file. (Note: session 1 will not, as many people think, roll back the entire transaction, it will roll back only the single statement that allowed the deadlock to develop). Session 2 will still be waiting for session 1 to commit or rollback its insert into parent.

Contrary to the popular claim, Oracle will not “resolve” the deadlock, it will simply break the deadlock leaving one session waiting for the other session to respond appropriately to the deadlock error.

For reference, here’s the deadlock graph (from a 12c trace file) produced by session 1 (SID = 3) for this demo:

Deadlock graph:
                                          ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name                             process session holds waits  process session holds waits
TX-00010017-000026C7-00000000-00000000          6       3     X             33     250           S
TX-000A000D-000026F8-00000000-00000000         33     250     X              6       3           S

session 3: DID 0001-0006-00000004       session 250: DID 0001-0021-00000041
session 250: DID 0001-0021-00000041     session 3: DID 0001-0006-00000004

Rows waited on:
  Session 3: no row
  Session 250: no row

When you see a deadlock graph with TX waits of type S (share, mode 4) it’s a very good bet that the wait has something to do with indexes – which may mean referential integrity as discussed here, but may mean collisions on primary keys, and may mean something to do with simple collisions on index-organized tables. You’ll notice that the “Rows waited on:” section shows no row – unfortunately in earlier versions of Oracle you may find a spurious row entry here because the wait information from some other (block) wait has been left in the relevant columns in v$session.


  1. Hi Jonathan.
    A note regarding your note:

    “Note: if this were a multi-statement transaction it would only be the insert into child that would be rolled back;”

    There is yet another “one of those details that is important but often isn’t stated explicitly” – if the multi-statement transaction is embedded in an anonymous block then all the statements within the anonymous block would be rolled back (as anonymous block is actually considered a single statement for this matter).


    Comment by Oren Nakdimon (@DBoriented) — May 12, 2016 @ 7:00 am BST May 12,2016 | Reply

    • Oren,

      Thanks for adding that detail – a good demonstration, I think, of how useful it is to have many eyes on a problem. What the world needs is an Oracle Wiki with an extremely competent editor to re-arrange all the bits and pieces into a cohesive (and correct) whole after the community has filled in all the gaps and special cases. Competent as an editor and as a technician, of course – and anyone who qualifies probably doesn’t have the time to do it.

      However, you’ve s also supplied an example of how easy it is to fail to state the extra, extra, little detail that is often overlooked:

      Session 1:
      Insert into parent values (3,'abcd');
      Session 2:
              insert into parent values (4,'asdf');
              insert into child values(3,1,'asdf');
              when others then null;
      Session 1 -- (to cause an error in session 2)
      Session 2 -- block completes
      select * from parent;
              ID NAME
      ---------- ------------
               1 Smith
               2 Jones
               4 asdf
      3 rows selected.

      All the SQL in the block rolls back if (a) none of it was committed in the block and (b) the block ended with an exception being (re-)raised.

      I know you shouldn’t have “when others then null”, of course, but it happens.

      Comment by Jonathan Lewis — May 12, 2016 @ 7:52 am BST May 12,2016 | Reply

  2. […] 10. RI Locks  […]

    Pingback by Deadlines Met and News @ OBIEE | Oracle Business Intelligence — June 4, 2016 @ 7:02 pm BST Jun 4,2016 | Reply

  3. […] Referential Integrity (May 2016): including a discussion of deadlocks due to concurrent processes mixing their inserts of parent and child rows. […]

    Pingback by Lock Catalogue | Oracle Scratchpad — February 19, 2022 @ 10:34 am GMT Feb 19,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: