Locking Improvements for Index Rebuild :
Red for session 1
Green for session 2
Blue for session 3
********************
*** 10g
********************
***
********************
*** 11g
********************
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;
*** In yet another session (3)
SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');
*** Now commit in session (2)
SQL-2> COMMIT;
Commit complete.
SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');
SQL-3> commit;
Commit complete.
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;
*** In yet another session (3)
SQL-3> INSERT INTO test_ind_table VALUES (10002, 'Srinivas');
1 row created.
SQL-2> commit;
Commit complete.
SQL-2> INSERT INTO test_ind_table VALUES (10003, 'Satish');
1 row created.
SQL-2> commit; (session 2)
Commit complete.
Index altered. (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
Posts
Post a Comment
Thank you for visiting our site and leaving your valuable comment.