Forum Stats

  • 3,814,961 Users
  • 2,258,937 Discussions
  • 7,892,898 Comments

Discussions

CREATE INDEX ONLINE waiting on TX mode=4

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Nov 11, 2015 12:46PM in General Database Discussions

Hi,

I have a strange locking issue where a CREATE INDEX ONLINE is waiting for a transaction that did only an inserted a row into the parent table.

Wait is on TX mode=4 (not TM locks) and I've currently reproduced it only on 11.2.0.3 and 11.2.0.4

Easy to reproduce on SCOTT schema:

Session 1:

SQL> set time on

14:54:58 SQL> insert into SCOTT.DEPT(deptno,dname) values (50,'test');

1 row created.

Session 2;

14:55:24 SQL> create index test on SCOTT.EMP (ename) ONLINE;

this is waiting on 'enq: TX - row lock contention':

SQL> select sid,chain_signature from v$wait_chains where blocker_is_valid='TRUE'

       SID CHAIN_SIGNATURE                                                      

---------- ----------------------------------------------------------------------

       603 'SQL*Net message from client'<='enq: TX - row lock contention'

waiting to acquire transaction lock in mode 4:

SQL> select * from gv$lock where sid=603

   INST_ID ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK

---------- ---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------

         1 00000006CDA05EB8 00000006CDA05F10        603 AE       352577          0          4          0        348          2

         1 00000006CDA06078 00000006CDA060D0        603 DL       779505          0          3          0        308          2

         1 00000006CD9FABE0 00000006CD9FAC38        603 DL       779505          0          3          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779505          0          2          0        308          2

         1 00007F28001F5028 00007F28001F5088        603 TM       779510          0          4          0        308          2

         1 00000006CD9FA240 00000006CD9FA298        603 OD       779505          0          4          0        308          2

         1 00000006B4F26790 00000006B4F26808        603 TX       589847     711499          6          0        308          2

         1 00000006CDA0ED68 00000006CDA0EDC0        603 TX       655377     996169          0          4        308          0

I've traced 10046 and 10704:

broadcast message=29231196672 obj#=779514 tim=1447251053634045

ksqgtl *** TX-000f001e-000ad97c mode=4 flags=0x10001 timeout=21474836 ***

ksqgtl: xcb=0x6bb322528, ktcdix=2147483647, topxcb=0x6bb322528

        ktcipt(topxcb)=0x0

ksucti: init session DID from txn DID:

ksqgtl:

        ksqlkdid: 0001-003F-0000AFC4

*** ksudidTrace: ksqgtl

        ktcmydid(): 0001-003F-0000AFC4

        ksusesdi:   0001-003F-0000AFC3

        ksusetxn:   0001-003F-0000AFC4

ksqcmi: TX,f001e,ad97c mode=4 timeout=21474836

*** 2015-11-11 15:11:36.566

WAIT #140700792578680: nam='enq: TX - row lock contention' ela= 42932082 name|mode=1415053316 usn<<16 | slot=983070 sequence=711036 obj#=779514 tim=1447251096566283

And there's nothing in V$SESSION current file/block/row. current obj# is EMP. current sql_id is the CREATE INDEX.

If anyone has an idea about:

- why creating an index online has to wait for transactions that modified only parent table

- how to investigate further

- can or cannot reproduce in other versions

Thanks in advance,

Franck.

Tagged:
Franck PachotPavan Kumarwbriceno

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,941 Blue Diamond
    edited Nov 11, 2015 9:54AM Answer ✓

    I assume you've got a foreign key constraint between emp and dept.

    This is expected behaviour: since 11.1 any DML at one end of a referential integrity constraint results in a mode 3 lock being taken at the other end, and your phenomenon looks like a side effect of that.

    When you try to create the index online your session has to assume that the other session (holding mode 3 on both emp and dept) may have modified both emp and dept, so it has to wait for the session to commit or rollback.

    (I suppose that in principle it could walk the undo for the transaction to see if EMP had actually been modified - but maybe that's seen as too complicated to be worth the risk of implementing)

    Regards

    Jonathan Lewis

    Update - I've just done a quick check to reproduce the effect on 11.1.0.7

    For reference - here are a few notes on lock modes and the way the implementation has changed over time: https://jonathanlewis.wordpress.com/2010/06/21/locks/

    Franck Pachot

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,941 Blue Diamond
    edited Nov 11, 2015 9:54AM Answer ✓

    I assume you've got a foreign key constraint between emp and dept.

    This is expected behaviour: since 11.1 any DML at one end of a referential integrity constraint results in a mode 3 lock being taken at the other end, and your phenomenon looks like a side effect of that.

    When you try to create the index online your session has to assume that the other session (holding mode 3 on both emp and dept) may have modified both emp and dept, so it has to wait for the session to commit or rollback.

    (I suppose that in principle it could walk the undo for the transaction to see if EMP had actually been modified - but maybe that's seen as too complicated to be worth the risk of implementing)

    Regards

    Jonathan Lewis

    Update - I've just done a quick check to reproduce the effect on 11.1.0.7

    For reference - here are a few notes on lock modes and the way the implementation has changed over time: https://jonathanlewis.wordpress.com/2010/06/21/locks/

    Franck Pachot
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Nov 11, 2015 10:23AM

    Hi Jonathan,

    Yes, but I was surprised to see a TX lock. The 'mode 3 lock being taken at the other end' is a TM lock.

    Then I think you mean that

    1. the CREATE INDEX ONLINE sees the TM Row-X

    2. instead of waiting on the lock (requesting a TM-Share for example as it did in 10g) it just waits for the end of session that holds the lock, through a TX mode=4.

    That may be the way they implemented the '_enable_online_index_without_s_locking' in 11g to avoid other DML being queued behind the create index.

    In 12c the insert acquires only a TM Row-S on child table and we don't see that TX wait.

    Thanks,

    Franck.

  • Ivica Arsov
    Ivica Arsov Member Posts: 173
    edited Nov 11, 2015 11:53AM

    Hi Franck,

    Lock 2 mode on child table was changed to 3 mode because of bug 5909305, check note Locking and Referential Integrity (Doc ID 33453.1).

    In order to force "lock 2 mode" you can set "_fix_control" = '5909305:OFF'

    The tests are done  11.2.0.4

    17:46:14 SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE    11.2.0.4.0      Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    

    Your case:

    session 1:

    SQL> set time on
    17:41:08 SQL> insert into SCOTT.DEPT(deptno,dname) values (50,'test');
    
    1 row created.
    

    session 2:

    SQL> create index test on SCOTT.EMP (ename) ONLINE;
    

    --> 'SQL*Net message from client'<='enq: TX - row lock contention'

    "workaround" -> with "_fix_control" = '5909305:OFF'

    session 1:

    SQL> set time on
    17:46:09 SQL> insert into SCOTT.DEPT(deptno,dname) values (50,'test');
    
    1 row created.
    
    17:46:14 SQL>
    

    session 2:

    17:46:04 SQL>
    17:46:28 SQL> create index test on SCOTT.EMP (ename) ONLINE;
    
    Index created.
    
    17:46:32 SQL>
    

    Regards

    Ivica Arsov

    Pavan Kumarwbriceno
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Nov 11, 2015 12:46PM

    Hi Ivica,

    Yes, that new behavior was a nightmare in 11g where locking consequences increased a lot. Penalizing DML concurrency to allow index rebuild...

    12c has released a few situations back to mode 2 so the test case above don't wait in 12c.

    Anyway, I don't think that it is expected to wait for DML on another table when creating an index. It's just a side effect of the way '_enable_online_index_without_s_locking' is implemented.

    Anyway, _enable_online_index_without_s_locking is good because you can't consider as online an operation that request TM-Share.

    Regards,

    Franck.

This discussion has been closed.