Home » , , » Locking Improvements for Index Rebuild

Locking Improvements for Index Rebuild

Written By Srikrishna Murthy Annam on Sunday, October 4, 2009 | 7:39 AM

Locking Improvements for Index Rebuild :

Red for session 1

Green for session 2

Blue for session 3

********************

*** 10g

********************

***
In session (1)

SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, 'Krishna Murthy' name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, 'Mahesh');

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;
*** session hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');
*** It now hangs due to lock from session 1 , as would a transaction on the test_ind_table table in session 4 and 5 and 6 and   ...

*** Now commit in session (2)

SQL-2> COMMIT;

Commit complete.
*** releases the lock in session 3 and the index rebuild is free to proceed but it will eventually get stuck again as it now requires another lock to complete the rebuild process ...

*** In session 2, perform another insert before session 3 commits ...

SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');
*** and now it in turn hangs due to the rebuild needing the second table lock

*** perform the commit in session (3)

SQL-3> commit;

Commit complete.
and it allows the rebuild in session 1 to finally finish and in turn allows the update in session 2 to then be released and complete as well

*** So a rebuild requires a lock at the start and at the end of the index rebuild process, even if performed ONLINE

*** These locks in turn cause other concurrent transactions on the table to hang as well


********************
*** 11g
********************
*** In session (1)
SQL-1> CREATE TABLE test_ind_table AS SELECT rownum id, 'Krishna Murthy' name FROM dual CONNECT BY LEVEL <= 10000;

Table created.

SQL-1> CREATE INDEX test_ind_table_i ON test_ind_table(id);

Index created.

*** In other session (2)

SQL-2> INSERT INTO test_ind_table VALUES (10001, 'Mahesh');

1 row created.

*** In the orginal session (1)

SQL-1> ALTER INDEX test_ind_table_i REBUILD ONLINE;
session still hangs (due to inability to get table lock due to session 2)

*** In yet another session (3)

SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');

1 row created.
*** Big change. This session is no longer impacted by the rebuild trying to get it's table lock. It can carry on happily ..

*** Performing a Commit in session 2 will allow the rebuild to commence but it will be stuck again with the incomplete transaction in session 3.

SQL-2> commit;

Commit complete.
*** Performing another insert in session 2 will complete fine as again the rebuild does not impact other transactions

SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');

1 row created.
*** commiting the transactions in both session 2 and 3 will allow the rebuild to finally complete

SQL-2> commit; (session 2)

Commit complete.

Index altered.  (session 1).
*** So an online rebuild in 11g can still be impacted by concurrent transactions but it in turn will not cause locking issues for other concurrent transactions on the base table

Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger