CREATE INDEX ONLINE waiting on TX mode=4

Franck Pachot · Nov 11, 2015 9:30AM

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.

Jonathan Lewis · Nov 11, 2015 9:49AM

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/

Jonathan Lewis · Nov 11, 2015 9:49AM

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 · 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 · 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

Franck Pachot · 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.

Forum Stats

Quick Links

Categories

CREATE INDEX ONLINE waiting on TX mode=4

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

CREATE INDEX ONLINE waiting on TX mode=4

Best Answer

Answers