Improved index locking in 11g

 

During the creation or rebuilding of an index online, Oracle still requires two associated table locks on the base table at the start and end of indexing process. If there’s an active transaction on the base table at the time one of these locks is required, the indexing process will still hang as it’s done previously until all these prior active transactions have completed. No change so far.

However, if the indexing process has been locked out and subsequent transactions relating to the base table start afterwards, these transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully (However prior to 11g these transactions will be locked out). The indexing process no longer impacts other concurrent transactions on the base table. It will be the only process potentially left hanging while waiting to acquire its associated lock resource.

 

/******************

In Oracle 10g database

*******************/

 

session#1

       create table t(x int,y varchar2(10));

       insert into t values(1,'a');

      

session#2

       create index t_idx on t(x) ONLINE;

      

session#1

rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

       142                 1              148 session#2  enq: TM - contention

       144                                    session#3  SQL*Net message from client

       148                                    session#1  SQL*Net message to client

       152                                               SQL*Net message from client

 

4 rows selected.

 

rajesh@ORA10G>

So, Session#2 is blocked by session#1

 

session#3    

       insert into t values(2,'b');

      

session#1

rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

       142                 1              148 session#2  enq: TM - contention

       144                 1              142 session#3  enq: TM - contention

       148                                    session#1  SQL*Net message to client

       152                                               SQL*Net message from client

 

4 rows selected.

 

rajesh@ORA10G>

 

So, Session#2 is blocked by session#1 & Session#3 is blocked by Session#2

 

rajesh@ORA10G> commit;

 

Commit complete.

 

rajesh@ORA10G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

       142                 1              144 session#2  enq: TM - contention

       144                                    session#3  SQL*Net message from client

       148                                    session#1  SQL*Net message to client

       152                                               SQL*Net message from client

 

4 rows selected.

 

rajesh@ORA10G>

 

So when session#1 commit, the session#2 is locked out by session#3

 

 

/******************

In Oracle 11g database

*******************/

 

session#1

       create table t(x int,y varchar2(10));

       insert into t values(1,'a');

      

session#2

       create index t_idx on t(x) ONLINE;

      

session#1

rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

        67                                    session#3  SQL*Net message from client

        68                                    session#1  SQL*Net message from client

       134                 1               68 session#2  enq: TX - row lock contention

 

3 rows selected.

 

rajesh@ORA11G>

 

So, Session#2 is blocked by session#1 (as usual, similar to 10g database)

 

session#3    

       insert into t values(2,'b');

      

session#1

rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

        67                                    session#3  SQL*Net message from client

        68                                    session#1  SQL*Net message from client

       134                 1               68 session#2  enq: TX - row lock contention

 

3 rows selected.

 

rajesh@ORA11G>

 

Session#3 is not being locked out. (Were as in 10g it is locked out)

 

rajesh@ORA11G> commit;

 

Commit complete.

 

rajesh@ORA11G> select sid,blocking_instance,blocking_session,client_info,event

  2  from v$session s

  3  where username = user ;

 

       SID BLOCKING_INSTANCE BLOCKING_SESSION CLIENT_INF EVENT

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

        67                                    session#3  SQL*Net message from client

        68                                    session#1  SQL*Net message from client

       134                 1               67 session#2  enq: TX - row lock contention

 

3 rows selected.

 

rajesh@ORA11G>

 

Upon commit session#2 is locked out by session#3 (similar to 10g behavior)

 

So transactions will no longer in turn be locked out by the indexing table locks and are able to complete successfully in Oracle 11g databases.