MERGE and IOT’s ….. unhappy bedfellows

Anyone who has used Oracle for a while will be familiar with the Parent/Child locking “issue” when it comes to tables and indexes on foreign keys.  For many years you’d hear people crying “bug” etc but thankfully most now know the reason, and accept it as sensible behaviour.

But lets take a look at a slight variation on that theme.

Lets start with a table called “LOC” which will be our parent table in this example. Note that it is an IOT, and we’ll also have a child table “LOC_CHILD”, which is also an IOT.

SQL> CREATE TABLE LOC
  2  (
  3    LOC_ID              NUMBER(4)            NOT NULL,
  4    DATA                     NUMBER(6),
  5    CONSTRAINT LOC_PK
  6    PRIMARY KEY
  7    ( LOC_ID)
  8    ENABLE VALIDATE
  9  )
 10  ORGANIZATION INDEX
 11  /

Table created.

SQL> CREATE TABLE LOC_CHILD
  2  (
  3    CHILD_SEQ                  NUMBER(12)          NOT NULL,
  4    LOC_ID               NUMBER(4)           NOT NULL,
  5    CHILD_DATA  NUMBER(15,6),
  6    CONSTRAINT LOC_CHILD_PK
  7    PRIMARY KEY
  8    (CHILD_SEQ, LOC_ID)
  9    ENABLE VALIDATE
 10  )
 11  ORGANIZATION INDEX
 12  /

Table created.

SQL> insert into LOC
  2  select rownum,50
  3  from dual
  4  connect by level <= 5
  5  /

5 rows created.

Now being a good DBA 🙂 we’ve read all the “gloom and doom” nonsense about foreign keys being indexed, so just to be careful, we’ll add that index onto our child table before adding our foreign key back to LOC.

SQL> CREATE INDEX LOC_CHILD_IX ON LOC_CHILD
  2  (LOC_ID)
  3  /

Index created.

SQL> ALTER TABLE LOC_CHILD ADD (
  2    CONSTRAINT LOC_CHILD_FK
  3    FOREIGN KEY ( LOC_ID)
  4    REFERENCES LOC (LOC_ID)
  5    ENABLE VALIDATE)
  6  /

Table altered.

SQL> insert into LOC_CHILD
  2  select rownum,mod(rownum,5)+1,dbms_random.value(1000,5000)
  3  from dual
  4  connect by level <= 2000
  5  /

2000 rows created.

SQL> commit;

Commit complete.

So the scene is set..we’ve got our tables seeded with some data, and ready to go..

Lets update a row in the parent table LOC:

SQL> UPDATE loc
  2  SET    DATA = 99
  3  WHERE  LOC_ID = 2;

1 row updated.

Now we’ll pop into a new session and update the child table LOC_CHILD:

SQL> MERGE 
  2       INTO  LOC_CHILD
  3       USING (SELECT 500 CHILD_SEQ,
  4                     2 LOC_ID,
  5                     1000 CHILD_DATA
  6                FROM DUAL) M
  7          ON (    LOC_CHILD.CHILD_SEQ = M.CHILD_SEQ
  8              AND LOC_CHILD.LOC_ID = M.LOC_ID)
  9  WHEN MATCHED
 10  THEN
 11     UPDATE SET
 12        LOC_CHILD.CHILD_DATA =  NVL (LOC_CHILD.CHILD_DATA, 0) + M.CHILD_DATA
 13  WHEN NOT MATCHED
 14  THEN
 15     INSERT     (CHILD_SEQ,
 16                 LOC_ID,
 17                 CHILD_DATA)
 18         VALUES (M.CHILD_SEQ,
 19                 M.LOC_ID,
 20                 M.CHILD_DATA);

[stuck]

And splat…we’re stuck.  (Without evidence to support it) I’d hypothesize its due to the flexibility of the merge command.  A single merge can insert, update and even delete rows, so I’m guessing that to handle this flexibility (in particular, the DELETE option) then the locking errs on the side of safety.

Advertisements

7 thoughts on “MERGE and IOT’s ….. unhappy bedfellows

  1. Hello Connor,

    I slightly modified you test case:
    1. maked LOC_CHILD table HOT (heap-organized)
    2. changed merge to INSERT:
    insert into loc_child( child_seq, loc_id, child_data) values( 500, 2, 1000);

    Session 2 is requested TX lock in mode 4:

    SQL> select * from v$lock where request>0;
    
    ADDR             KADDR                   SID TYPE          ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
    ---------------- ---------------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
    000000045D525BA0 000000045D525C00         17 TX         720915      14757          0          4          5          0          0
    

    That is probably IOT/FOREIGN KEY/LOCKING issue, not a MERGE issue.
    Gathered additional information: index block dumps, trace files of event 10704 (enqueues) and DTrace’d server process (using Tanel Poder qer_trace.sh modified for catching kcbgcur calls – current block gets) – still it is not clear why Oracle behave in this way.
    We actively using IOTs in our applications and this issue can negatively affect us.
    Do you plan to create Service Request?

  2. Hi Mikhail,
    Thanks for exploring the topic further.
    For my client, we worked around the issue by modifying some of the processes that the application used, so have not logged an SR.
    But I agree with you – I can’t see why Oracle has adopted such a stance when it comes to IOT’s
    Cheers,
    Connor

  3. Connor,

    I think it’s a necessary wait because of the potential for deadlocks in odd scenarios. The critical features is that you don’t have a table row for the parent, only an index entry – so the lock has to be on the index entry. In theory Oracle Corp could probably write some code to allow the child update to determine that the lock existed because a non-key column had been changed, in practice it simply says “the pk entry is locked, I have to wait for the commit/rollback”.

    You can see the same effect with a pure heap-table example if you support the PK with an index that has a non-key column added to it and you update the non-key column.

  4. Hello,

    You can see the same effect with a pure heap-table example if you support the PK with an index that has a non-key column added to it and you update the non-key column.

    I’m apologize, but PK with non-key column behaves slightly differently.
    Old index entry after update marked as deleted and new index entry is created:

    SQL> create table t1(x int, y int, constraint t1_pk primary key(x) using index (create index t1_pk on t1(x,y)));
    Table created.
    SQL> insert into t1 values (1,1);
    1 row created.
    SQL> commit;
    Commit complete.
    SQL> select object_id from obj where object_name='T1_PK';
     OBJECT_ID
    ----------
        329184
    SQL> select dbms_rowid.rowid_relative_fno(rid) file#, dbms_rowid.rowid_block_number(rid) block from (select sys_op_lbid( 329184, 'L', rowid) rid from t1 where x=1);
         FILE#      BLOCK
    ---------- ----------
            12     834475
    SQL> alter system checkpoint;
    System altered.
    SQL> alter system dump datafile 12 block 834475;
    System altered.
    SQL> update t1 set y=2 where x=1;
    1 row updated.
    SQL> alter system checkpoint;
    System altered.
    SQL> alter system dump datafile 12 block 834475;
    System altered.
    
    -- index block dump
    
    row#0[8017] flag: ---D--, lock: 2, len=15
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 02
    col 2; len 6; (6):  03 0c bb a3 00 00
    row#1[8002] flag: ------, lock: 2, len=15
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 03
    col 2; len 6; (6):  03 0c bb a3 00 00
    

    IOT after non-key update not create new index entry and marked index entry as deleted only after key update:

    -- before
    
    row#0[8018] flag: K-----, lock: 0, len=14
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 03
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 04
    
    -- after nonkey update
    
    row#0[8018] flag: K-----, lock: 2, len=14
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 03
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 05
    
    -- after key update
    
    row#0[8018] flag: K--D--, lock: 2, len=14
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 03
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 04
    row#1[8004] flag: K-----, lock: 2, len=14
    col 0; len 2; (2):  c1 02
    col 1; len 2; (2):  c1 04
    tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
    col  0: [ 2]  c1 04
    

    I’m dont know clearly why Oracle cannot check index entry flag to differentiate key/non-key updates in our case.

  5. Hello,
    I created SR 3-9505364811 : IOT FOREIGN KEY LOCKING 2 months ago with providing link to this blog post.
    3rd of September support engineer opened new Bug 19552277 : UPDATE ON IOT PARENT TABLE BLOCKS DML ON CHILD TABLE.
    After 2 weeks that bug was closed with status “32 – Not a Bug. To Filer”.
    4th of November support engineer opened new documentation Bug 19949828 : NEED TO DOCUMENT THE LIMITATION OF IOT PARENT TABLE AND FOREIGN KEY CONSTRAINTS.
    It reminds me of phrase “a documented bug is feature” :).
    Still this’s not clear for me, why Oracle Corp could not improve their code as Jonathan suggested.
    At least, documentation will be updated with IOT locking restrictions.

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