Oracle Scratchpad

February 15, 2010

Lock Horror

Filed under: Infrastructure,Locks,Oracle,Troubleshooting — Jonathan Lewis @ 10:34 pm BST Feb 15,2010

Here’s an extract from a  simple script I’ve run from time to time over the last 10 years to create a little testbed to check up on “foreign key locking”. I haven’t run it for some time (my notes say the last version I ran it on until very recently was 10.1.0.2). All it does is create a pair of tables with a foreign key constraint between them and an index protecting that constraint from the foreign key locking problem:

create table parent (
	id	number(4),
	name	varchar2(10),
	constraint par_pk primary key (id)
)
;

create table child(
	id_p	number(4)
		constraint chi_fk_par
		references parent,
	id	number(4),
	name	varchar2(10),
	constraint chi_pk primary key (id_p, id)
)
;

insert into parent values (1,'Smith');
insert into parent values (2,'Jones');

insert into child values(1,1,'Simon');
insert into child values(1,2,'Sally');

insert into child values(2,1,'Jack');
insert into child values(2,2,'Jill');

commit;

Following up a blog note from Charles Hooper on deadlocks in 11g, and then more recently a note on the OTN database forum, I was moved to do some testing on 10.2.0.1, 10.2.0.3, and 11.1.0.6 - the preliminary results were worrying. Initially all I wanted to do was check what locks appear when you insert or delete child or parent rows, and then repeat the tests with table locking disabled on the child table.

Here are some results from 10.2.0.3

rem	Insert into parent - child is locked in mode 2, parent in mode 3
rem	Delete from parent - child is locked in mode 2, parent in mode 3
rem	Delete from child - parent is locked in mode 2, child in mode 3
rem	Insert into child - parent is locked in mode 2, child in mode 3

This was as I expected - the mode 2 (row share) lock at the "opposite end" of the foreign key constraint is something that appeared in 9.2.0.1. As you will see if you read my comment on Charles' blog item this mode 2 changes to a mode 3 (row exclusive) in 11g - a change described in Metalink bug note 5909305 and described as a "Notable Fix" in Metalink note 454506.1.

For each test I was executing a statement such as 'insert into parent ...', then running a query to check for the locks I held, then rolling back. The query I ran to check for locks was as follows:

select
	type, id1, id2, lmode, request
from
	v$lock
where
	sid = (select sid from v$mystat where rownum = 1)
;

After the first pass of tests listed above I started to repeat the sequence of tests after issuing "alter table child disable table lock;". The effect of this change makes it impossible to lock the table in modes 4, 5, or 6 (though you can still lock the table in modes 2 or 3 - respectively the "select for update" mode (until 9.2.0.5) and the "insert/update/delete" mode.

Check this specific sequence of events, starting from a fresh build of the tables:

SQL> alter table child disable table lock;

Table altered.

SQL> insert into parent values(3,'asdf');

1 row created.

SQL> select
  2     type, id1, id2, lmode, request
  3  from
  4     v$lock
  5  where
  6     sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TX     655405       2025          6          0

1 row selected.

Look at that output carefully: I've just inserted a row into the parent table after disabling locks on the child table. My session is reporting NO table locks - despite an uncommitted row in the parent table !

Going to another session, look at this cut-n-paste (which demonstrates that I should be able to see a lock on the parent, even though I don't expect to see a lock on the child (you'll have to trust me that 49638 is the object_id of the parent at this point):

SQL> lock table parent in row exclusive mode;

Table(s) Locked.

SQL> lock table child in row exclusive mode;

Table(s) Locked.

SQL> select
  2     type, id1, id2, lmode, request
  3  from
  4     v$lock
  5  where
  6     sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TM      49638          0          3          0

1 row selected.

And here's the "shock, horror, gasp" effect:

SQL> lock table parent in exclusive mode;

Table(s) Locked.

SQL> select
  2      type, id1, id2, lmode, request
  3   from
  4      v$lock
  5   where
  6      sid = (select sid from v$mystat where rownum = 1)
  7  /

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
TM      49638          0          6          0

1 row selected.

This session is now holding an exclusive lock on the parent table when another session has an uncommitted row inserted into it.

I haven't checked if there's a bug report for this on Metalink (MOS), nor what happens in 10.2.0.4, 11.1.0.7 or 11.2.0.1. I haven't played around with changing the child table to an index organized table (IOT), or even checked whether (unlikely though it may seem) this behaviour is just an odd side effect of the slightly confusing column names I've used. And I haven't considered creating multiple child tables to check for stranger side effects (which is what the OTN posting was about).

If your code  uses "disable table lock" anywhere, though, you might want to do some very careful checks to make sure it's still behaving for you.

Update - the following morning:
Here are some results from 11.1.0.6 after creating the tables and indexes as above, then restarting the session - a single cut-n-paste of an SQL*Plus sessions. (I've saved the query against v$lock into afiedt.buf to avoid cluttering the page.) Comments appear after each output:

SQL> insert into parent values (3,'asd');

1 row created.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0
TM      86862          0          3          0
TM      86864          0          3          0
TX     327690      18287          6          0

4 rows selected.

When we insert into the parent, the child gets locked in mode 3 (row exclusive), not mode 2 (row share). (The AE lock is the 11g "Application Edition" lock).

SQL> commit;

Commit complete.

SQL> delete from parent where id = 3;

1 row deleted.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0
TM      86862          0          3          0
TM      86864          0          3          0
TX     262148      13832          6          0

4 rows selected.

When we delete a row with no children - the one we've just inserted - we take a mode 3 lock on the child.

SQL> rollback;

Rollback complete.

SQL> alter table child disable table lock;

Table altered.

SQL> delete from parent where id = 3;
delete from parent where id = 3
            *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for CHILD

Since we rolled back the delete, the parent row 3 will still be available for deletion. If we try to delete it with table locking disabled on the child we fail because we can't lock the child.

SQL> lock table child in row exclusive mode;

Table(s) Locked.

SQL> @afiedt.buf

TY        ID1        ID2      LMODE    REQUEST
-- ---------- ---------- ---------- ----------
AE         99          0          4          0

1 row selected.

SQL>

On the other hand, we can lock the child table in mode 3 (row exclusive) - even though the lock doesn't show up in v$lock. (And we can insert into the parent without running into a locking problem - apart from the fact that the expected mode 3 lock doesn't exist in v$lock and the table can be locked exclusively from another session).

The good news: I haven't test this yet on the latest versions (10.2.0.4, 11.1.0.7, 11.2.0.1) so perhaps it's all fixed with backports available.

Footnote:
In the past I've often advised people that if they want to have a bit of protection when they think it might be safe to drop a particular foreign key index they could always disable table locking on the child table (thus restricting the ensuing problems to the session that wasn't supposed to be locking the table anyway). That piece of advice is not safe until further notice.

33 Comments »

  1. Jonathan,

    I think there might a small glitch at this (important) point:

    “And here’s the “shock, horror, gasp” effect:

    SQL> lock table parent in row exclusive mode;”

    Shouldn’t this read “lock table parent in exclusive mode;”?

    Besides that – I can reproduce on 10.2.0.4 and what is similarly irritating – after acquiring the exclusive lock on the parent table I can still happily insert into the parent table from any other (including the first session), although the exclusive lock is showing up in V$LOCK – that is quite irritating.

    Randolf

    Comment by Randolf Geist — February 16, 2010 @ 3:00 pm BST Feb 16,2010 | Reply

    • Randolf,

      Thanks for that – I’ve re-run the test cycle just in case something really odd happened, but I think I just cut out the wrong set of “diagnostic comments” when I copied the output.

      Now corrected.

      Comment by Jonathan Lewis — February 16, 2010 @ 6:44 pm BST Feb 16,2010 | Reply

  2. This is really horrible and scary. I had the same effect on 11.2.0.1 on Linux and 10.2.0.4 on Solaris.

    I don’t understand something though, afaik my session needs to acquire row exclusive mode lock to do insert/update/delete on a table but what happens in my case is “lock table parent in row exclusive mode;” does not work but insert/update/delete works (What I mean here is I can still continue to insert/update/delete on the parent while another session hold the exclusive lock ).

    Does this means locking explicitly does extra checks but implicit locking in insert/update/delete doesn’t ?

    Comment by coskan — March 15, 2010 @ 1:45 am BST Mar 15,2010 | Reply

    • Coskan.

      I think what you’re saying is that an insert/update/delete on a table should acquire a mode 3 lock – but you can still do these operations even though you’ve disabled table locking. I don’t know how Oracle gets around this. If you have table locks enabled then an insert/update/delete will create a row in x$ktadm to show a mode 3 lock (ksqlkmod = 3). If you disable table locking this row doesn’t appear – so Oracle must have a different code path to allow this to happen.

      Comment by Jonathan Lewis — March 20, 2010 @ 12:33 pm BST Mar 20,2010 | Reply

  3. [...] 16-Locking problem/bug while locking parent table exclusively when locking child is disabled Jonathan Lewis-Lock Horror [...]

    Pingback by Blogroll Report 12/02/2009 – 19/02/2010 « Coskan’s Approach to Oracle — March 18, 2010 @ 4:44 pm BST Mar 18,2010 | Reply

  4. [...] Since 11.1 used at opposite end of RI during DML (See Lock Horror) 4 Share (S) Lock table in share mode Can also appear during parallel DML with id2 = 1 Common [...]

    Pingback by Locks « Oracle Scratchpad — June 21, 2010 @ 9:56 pm BST Jun 21,2010 | Reply

  5. Following up a question on OTN, I’ve found that bug note 5909305 is no longer visible on Metalink; but a search in the bug database for that bug number returns several hits that reference it, including a note (see bug 8881121, for example) that the change can be reverted through the “fix control” parameter, by setting “_fix_control” to ’5909305:OFF’

    Comment by Jonathan Lewis — June 22, 2010 @ 6:07 am BST Jun 22,2010 | Reply

  6. Bug note 5909305 is visible again.

    Comment by al0 — February 14, 2011 @ 3:10 pm BST Feb 14,2011 | Reply

  7. I’ve tested this out in 11.1.0.7 EE on RHEL x86_64. I’m convinced this is a bug. Why does Oracle ever need a lock on the child table when inserting into the parent (either Mode 2 or Mode 3)..? It doesn’t make sense to me. Even when I add an index to the child (foreign key) column Oracle still takes out the lock on the child – is that expected behaviour..? Test case below….

    Session 1 (NORMAL USER):

    create table parent (id number primary key, parent_descr varchar2(10));
    
    create table child (id number primary key, parent_id number, child_descr varchar2(10));
    
    alter table child add constraint child_fk foreign key (parent_id) references parent(id);
    
    insert into parent values (1,'Parent One');
    insert into child values (1,1,'Child One');
    commit;
    
    insert into parent values (2,'Parent Two');
    

    Session 2 (SYSDBA):

    select sid from v$session where program like 'sqlplus%' and username = 'ORER';
    
    col object_name form a20
    select l.type, do.object_name, l.id2, l.lmode, l.request
    from v$lock l 
    left outer join dba_objects do on do.object_id = l.id1
    where sid = 524
    
    
    TY OBJECT_NAME                      ID2      LMODE    REQUEST
    -- ------------------------- ---------- ---------- ----------
    TM CHILD                              0          3          0
    TM PARENT                             0          3          0
    AE ORA$BASE                           0          4          0
    TX                                37739          6          0
    

    Session 1 (NORMAL USER):

    rollback;
    
    create index childidx on child(parent_id);
    
    insert into parent values (2,'Parent Two');
    

    Session 2 (SYSDBA):

    select sid from v$session where program like 'sqlplus%' and username = 'ORER';
    
    col object_name form a20
    select l.type, do.object_name, l.id2, l.lmode, l.request
    from v$lock l 
    left outer join dba_objects do on do.object_id = l.id1
    where sid = 524
    
    TY OBJECT_NAME                      ID2      LMODE    REQUEST
    -- ------------------------- ---------- ---------- ----------
    TM CHILD                              0          3          0
    TM PARENT                             0          3          0
    AE ORA$BASE                           0          4          0
    TX                                63935          6          0
    

    Comment by Matt McClernon — February 14, 2011 @ 11:26 pm BST Feb 14,2011 | Reply

    • Matt,

      I can’t work out why the “lock at opposite end” comes into play, but I have a note to myself that this was introduced to handle a possible deadlock when executing parallel DML on tables with referential integrity constraints. I can’t find anything on Metalink (MOS) about this, and the closest match is bug 2435787)

      Comment by Jonathan Lewis — February 15, 2011 @ 9:12 am BST Feb 15,2011 | Reply

      • The closest I can find on Metalink is: https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=223303.1

        but it’s still not clear from that note.

        The thing that bothers me is that Richard Foote suggests that the lock on the child is not taken when an index is placed on the foreign key column. At the bottom of this post: http://richardfoote.wordpress.com/2010/11/10/oracle11g-new-locking-modes-when-policing-fk-constraints-a-wolf-at-the-door/ he says:

        “The “fix” in this case is to simply create an index on the formats_id FK column …snipped… In which case the table share lock is no longer required on the ALBUMS table (as Oracle can now use the associated index to effectively police the integrity of the child table following such an operation on a parent table)”

        Comment by Matt McClernon — February 15, 2011 @ 9:44 pm BST Feb 15,2011 | Reply

        • Matt,

          The lock that Richard is talking about is the mode 4 lock that gets taken if you don’t have the “foreign key” index in place and you try to modify the parent key value or delete the parent row.

          The lock we’re talking about in this article is a mode 2 (lately mode 3) lock that gets taken out when you modify the NON-KEY columns, or insert a new row, at either end of the foreign key.

          Comment by Jonathan Lewis — February 18, 2011 @ 7:25 pm BST Feb 18,2011

  8. Dear Jonathan,

    with reference to the below point

    “This was as I expected – the mode 2 (row share) lock at the “opposite end” of the foreign key constraint is something that appeared in 9.2.0.1″.

    But as per documents (Database Concepts Guide 9i, 10g and 11g)
    “Inserts into the parent table do not acquire table locks on the child table.”

    If you could please throw some light on this.

    Comment by Vijay — April 29, 2011 @ 11:57 am BST Apr 29,2011 | Reply

      • Richard Feynman: “It doesn’t matter how beautiful your theory is, it doesn’t matter how smart you are. If it doesn’t agree with experiment, it’s wrong.”

      It’s easy enough to test – the manuals are wrong.

      Comment by Jonathan Lewis — April 29, 2011 @ 12:36 pm BST Apr 29,2011 | Reply

  9. Jonathan,

    Thanks for taking out time and responding to my query.

    I don’t have Oracle DB 8i and 9i to test this but as per your comment

    Mode 2 (row share) lock are taken at opposite end of foreign key, Oracle 9.2.0.1 onwards.

    Am I correct in my understanding on this?

    Comment by Vijay — May 2, 2011 @ 6:18 am BST May 2,2011 | Reply

  10. Hi Jonathan,

    The below testcase is not directly related to foreign key locking, so I hope I can post it here. If that’s not the case, I apologise in advance and if you find it necessary, you can freely delete it.

    I hope you can give an opinion if such a behavior of v$lock is expected, especially since it shows up only in 11.x versions, but not in 10.2.

    I executed the code below on a 11.2.0.1.0 2-node RAC cluster. Both sessions where on the first instance and I changed the sqlprompt to display the session id.

    In the first session I create a table and lock it in share mode:

    SID = 65> create table t(id number);
    
    Table created.
    
    SID = 65> lock table t in share mode;
    
    Table(s) Locked.
    

    In the second session I lock the same table in row share mode:

    SID = 1784> lock table t in row share mode;
    
    Table(s) Locked.
    

    Then in a separate session I check the acquired locks. As expected, session 65 holds a mode 4 TM lock and session 1784 a mode 2 TM lock.

    SQL>select
      2       sid, type, id1, id2, lmode, request, block
      3    from
      4       v$lock
      5    where
      6       sid in (65, 1784)
      7    order by
      8       sid, type
      9  /
    
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
    ------ -- ---------- ---------- ---------- ---------- ----------
        65 AE        100          0          4          0          2
        65 TM     148298          0          4          0          2
      1784 AE        100          0          4          0          2
      1784 TM     148298          0          2          0          2
    

    In session 65 I try to lock the table in exclusive mode and it hangs as it has to wait for session 1784 to release its lock:

        
    SID = 65> lock table t in exclusive mode;  
    

    And now what seems strange to me. When I check the status in v$lock, I see that both sessions report BLOCK=1 for TM locks. If I understand correctly, only session 1784 should report BLOCK=1 since it actually blocks session 65. On the other hand, session 65 doesn't block anyone:

    SQL>/
    
       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
    ------ -- ---------- ---------- ---------- ---------- ----------
        65 AE        100          0          4          0          2
        65 TM     148298          0          4          6          1
      1784 AE        100          0          4          0          2
      1784 TM     148298          0          2          0          1
    

    The same behavior is also reproducible on a 11.2.0.1 and 11.1.0.6 single instance database. On the other hand, it's different on a 10.2.0.4 database (single instance). After repeating all the above steps in the same sequence in two sessions, I get the following state in v$lock, which seems reasonable to me:

           SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
    ---------- -- ---------- ---------- ---------- ---------- ----------
           142 TM      65818          0          2          0          1
           148 TM      65818          0          4          6          0
    

    I discovered this when trying to reproduce a locking problem on a 11.2.0.1 instance and "utllockt.sql" returned "ORA-01436: CONNECT BY loop in user data" instead of displaying the lock wait graph :-)

    Thanks in advance for any feedback

    Comment by Jure Bratina — May 18, 2011 @ 8:44 pm BST May 18,2011 | Reply

    • Jure,

      Your observation isn’t directly connected – but it’s another symptom of “notable changes in behaviour” in the same area of functionality, which makes it an appropriate place to raise the issue.

      I don’t have any sound comment to make about why it’s happening, or whether it’s really a bug.

      If I were to take a guess about intentions – in RAC any lock is recorded as “block = 2″ because it might be a blocker of another instance; perhaps in 11.2 Oracle then changes any blocked lock (as well as its blocker) from 2 to 1 because a blocked lock is more likely to become a real, direct, blocker to another instance. (This is pure supposition, of course, and doesn’t answer the question of why this should also appear as a change in single instance Oracle.)

      It’s also worth noting that there are a couple of bug fixes reported in 11.2.0.2 relating to deadlocks across database links – perhaps this extra block indication relates to that.

      Comment by Jonathan Lewis — May 22, 2011 @ 12:38 pm BST May 22,2011 | Reply

  11. Jonathan, it seems that the guy who wrote the “Oracle® Database Concepts 11g Release 2 (11.2)” doesnt know this behavior. Please, see the book:
    http://download.oracle.com/docs/cd/E14072_01/server.112/e10713/consist.htm#i5704
    and search for:
    “Inserts into the parent table do not acquire table locks on the child table.”

    Also,i did some tests in my test enviroment: 11.2.0.1, and it looks like Oracle does not implement FIFO anymore on 11g. You can see the behavior as well, just do the following steps:

    First Command User 1: DELETE parent_table WHERE pk_column = X;

    – after –

    First Command User 2: DELETE parent_table WHERE pk_column = X;

    – after –

    First Command User 3: INSERT INTO parent_table VALUES (X, X);

    – after –

    Second Command User 1: commit;

    And see what is going on with the session two and three :)

    Do you think this is a normal behavior? I can’t conclude because i don’t have an Oracle 10g…

    Comment by Thiago Maciel — June 4, 2011 @ 3:54 am BST Jun 4,2011 | Reply

    • Thiago,

      There are lots of places where the manuals (and notes on Metalink) have not changed to reflect the way the software has changed. From time to time I use the “feedback” option to point out errors or omissions. You could do the same in this case.

      I can’t comment on your question on locking because I didn’t see anything anomalous when I tried to emulate your code path on 11.2.0.2. (It behaved the same way on 9.2.0.8 and 10.2.0.3)

      It seems to me, though, that there are two possible legal outcomes – although one of them is counter-intuitive.

      a) Session 2 reports no rows deleted and session 3 inserts a row
      b) Session 3 inserts a row and session 2 waits on session 3 to commit (actually, I’ve changed my mind about that, at least for the simplest cases – although if there is a child table involved I might want to re-think the issue).

      Option (a) is the intuitively obvious one.

      Option (b) could occur, in some circumstances perhaps, but is highly dependent on timing – if you are seeing it then it may be a legal example of “write-consistency”. Session 2 has to rollback and restart because the data it is supposed to modify has changed since the start of statement; this being the case it is possible for session 3 to be released to insert the new row. (It’s also possible that you’ve found a bug, of course.)

      I haven’t been able to make (b) appear on 11.2.0.2.

      Have you got a complete demonstration showing how to create the table, and listing every step along the way. Have you checked that you don’t have some setting (e.g. related to isolation level) that confuses the issue ?

      Comment by Jonathan Lewis — June 4, 2011 @ 10:56 am BST Jun 4,2011 | Reply

  12. Hi Jonathan, thanks for the feedback. Yes, the answer is B, but I m still confused because of the FIFO implementation. My demonstration include “on delete cascade” on the child table, sorry i forgot to mention that. Below you can see the complete demonstration:

    CREATE TABLE SUPPLIER
      (
        "SUPPLIER_ID"   NUMBER(10,0) NOT NULL ENABLE,
        "SUPPLIER_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
        "CONTACT_NAME"  VARCHAR2(50 BYTE),
        CONSTRAINT "SUPPLIER_PK" PRIMARY KEY ("SUPPLIER_ID") 
      );  
      
    CREATE TABLE PRODUCT
      (
        "PRODUCT_ID"   NUMBER(10,0) NOT NULL ENABLE,
        "PRODUCT_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
        "SUPPLIER_ID"  NUMBER(10,0) NOT NULL ENABLE,
        CONSTRAINT "FK_SUPPLIER" FOREIGN KEY ("SUPPLIER_ID") REFERENCES ."SUPPLIER" ("SUPPLIER_ID") ON
      DELETE CASCADE ENABLE
      );
      
    insert into SUPPLIER values (1,'Thiago Maciel','email');
    insert into SUPPLIER values (2,'Jonathan Lewis','Blog - lock-horror');
    commit;
    insert into PRODUCT values(1,'Oracle',1);
    insert into PRODUCT values(1,'Enqueue Kernel',2);
    commit;
    

    Session 1: DELETE SUPPLIER WHERE SUPPLIER_ID = 1;
    Session 2: DELETE SUPPLIER WHERE SUPPLIER_ID = 2;
    Session 3: INSERT INTO SUPPLIER VALUES (3,'lock Behavior 11gR2','Oracle Support');

    Wait Chains: Session 2 is being blocked by the session 1, and session 3 is being blocked by the session 2.

    Session 1: commit;
    Session 2: still blocked
    Session 3: got the lock, and complete the insert command.

    Wait Chains: Now, session 2 is being blocked by the session 3

    Session 3: commit;
    Session 2: got the lock, and complete the delete command.
    Session 2: commit;

    Session 1: select * from supplier;
    SUPPLIER_ID SUPPLIER_NAME       CONTACT_NAME
    ----------- ------------------- --------------
              3 lock Behavior 11gR2 Oracle Support
    

    Comment by Thiago Maciel — June 4, 2011 @ 4:55 pm BST Jun 4,2011 | Reply

    • Thiago,

      That’s a lot easier to deal with. Forget all the stuff about write-consistency, I was fooled by the repetition of the ‘X’s in your original comment into thinking that you were deleting and re-inserting the same thing all the time. The example you’ve supplied arises through a combination of two things: (a) absence of a foreign key index when deleting parent keys (especially with “on delete cascade” on the foreign key, and (b) the change in 11g results in a mode 3 lock at the opposite end of a parent/child relationship rather than the mode 2 that used to be used in 9i and 10g.

      The sequence is as follows:

        Session 1 deletes from parent – gets mode 3 on parent, gets mode 5 on child because of absence of “foreign key” index, but ends up down-converting to mode 3.
        Session 2 locks parent in mode 3, attempts to lock child in mode 5 – gets blocked by session 1
        Session 3 locks parent in mode 3, attempts to lock child in mode 3 – gets blocked by session 2

        Session 1 commits

        Session 2 gets mode 5, and immediately down-converts to mode 3 (that’s just the way it works)
        Session 3 is no longer blocked and gets mode 3 on the child, does the insert on the parent
        Session 2 prepares to do the delete on the child and tries to up-convert to mode 5 – and gets blocked by session 3.

      The oddity (or rather, the behaviour that I can’t think of an explanation for yet) is the way that Oracle down-converts from 5 to 3 when it does, forcing it to up-convert to do the delete. You could raise this with Oracle in an SR, but I suspect they would simply tell you to create an index on the foreign key.

      The reason you don’t see the apparent breaking of FIFO in earlier versions of Oracle is that session 3 only takes out a mode 2 on the child in 9i and 10g when inserting on the parent, and session 2 is allowed to hold a mode 5 when other sessions are holding mode 2.

      Comment by Jonathan Lewis — June 5, 2011 @ 12:03 pm BST Jun 5,2011 | Reply

      • I was not aware of the way that Oracle down-converts from 5 to 3 immediately.

        This was a demo that i made to a jr performance analyst to show him the concepts (performance impact) of not having an index on the foreign key. So, when i was saying that insert on the parent table should not get blocked on this specific case, i made “oops, this is odd”. Now i can explain thanks to you.

        Moral of the story: Bad application design can stay even worse on 11g releases :)

        Comment by Thiago Maciel — June 5, 2011 @ 3:53 pm BST Jun 5,2011 | Reply

        • Thiago,

          I wasn’t aware of the down-up-down either until your demonstration prompted me to run a 10704 trace to see exactly what the locking sequence was.

          I think the comment I made on the “merge – argh!” note applies here: the example is just far enough outside the obvious list of tests that it wouldn’t be surprising if this side effect of the mode 2 / mode 3 change hadn’t been noticed. After all, I think you have to have a “missing” index and an insert that queues on delete or update that is already queueing on an uncommitted delete or update; I would imagine any test suite that defines a foreign key as “on delete cascade” would automatically include the foreign key index.

          Comment by Jonathan Lewis — June 5, 2011 @ 8:30 pm BST Jun 5,2011

  13. [...] from issuing a DDL statement to alter the table’s structure.” (pages 163-165 reference reference2 [...]

    Pingback by Book Review: Oracle Database 11g Performance Tuning Recipes « Charles Hooper's Oracle Notes — September 10, 2011 @ 9:14 pm BST Sep 10,2011 | Reply

  14. I just wanted to mention that I have a 10.2.0.3 and I see LMODE=3 (not 2) on the child table in 10.2.0.3 as well.

    Comment by thepisas — December 20, 2011 @ 11:26 pm BST Dec 20,2011 | Reply

    • I need to qualify my previous comment about observing mode 3 locking in 10.2.0.3 as well.

      The following tests were performed on 10.2.0.3

      test case 1: 1 parent table and 1 child table ; the FK is indexed and on delete cascade is defined.

      1a. delete on parent takes mode 3 lock on parent and mode 3 on child which is apparently not expected in 10.2.0.3 .
      1b. delete on child takes mode 3 on child and mode 2 on parent which is expected.

      test case 2: 1 parent table and 2 child tables referencing it; the FKs are indexed and on delete cascade is defined.

      1a. delete on parent takes mode 3 lock on parent and mode 3 on child1 and mode 2 on child 2 !!
      1b. delete on child takes mode 3 on child and mode 2 on parent.

      Comment by thepisas — December 21, 2011 @ 2:45 am BST Dec 21,2011 | Reply

      • test case 3: insert
        3a. insert into parent takes mode 3 lock on parent and mode 2 on all child(ren)
        3b.insert into child takes mode 3 on child and mode 2 on parent .

        Comment by thepisas — December 21, 2011 @ 2:47 am BST Dec 21,2011 | Reply

    • Thepisas,

      This is expected behaviour since, as you point our in your follow-up comment, you’ve declared your foreign key constraint to be “on delete cascade”. This means that “delete from parent” start with a recursive “delete from child” – which has to take the mode 3 lock.

      Comment by Jonathan Lewis — December 23, 2011 @ 10:13 am BST Dec 23,2011 | Reply

  15. […] 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, […]

    Pingback by Oracle Enqueue Rules Leading to Deadlock Changes Again in 12.1? | Charles Hooper's Oracle Notes — August 1, 2013 @ 9:27 pm BST Aug 1,2013 | Reply

  16. […] Intégrité référentielle, du côté opposé lors d’un DML (versions ≥ 11.1 – cf Lock Horror) […]

    Pingback by Verrous et signification du mode (lock mode), par Jonathan Lewis | Oracle – Concepts et Exemples — November 19, 2013 @ 4:03 pm BST Nov 19,2013 | Reply

  17. […] the more you have to check on each new release of the software. An incoming ping on my posting “Lock Horror” reminded me that I was writing about 11.2.0.1, and the terminal release is 11.2.0.4, and the whole […]

    Pingback by Juggernaut | Oracle Scratchpad — March 28, 2014 @ 8:12 am BST Mar 28,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers