Oracle Technologies Blog

By ASKM

Locking Improvements for Index Rebuild

Posted by Srikrishna Murthy Annam on October 4, 2009

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
Advertisements

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

 
%d bloggers like this: