【羅玄】從鎖的角度看rebuild index online和rebuild index

victorymoshui發表於2012-05-30
RebuildIndex

眾所周知，rebuild index online不會阻塞DML操作，而rebulid index卻會阻塞DML操作。現在我們來看一下在rebuild index online及rebulid index下的鎖

os: rhel 3
db: oracle 9.2.0.6

在Session 1下的操作

SQL> create table test_object tablespace  tbs_taobao as (select * from dba_objects);               
Table created.
SQL> insert into test_object (select * from test_object);
23926 rows created.
SQL> /
47852 rows created.
SQL> /
95704 rows created.
SQL> /
191408 rows created.
SQL> /
382816 rows created.
SQL> commit;
Commit complete.

說明：插入那麼多行，為了重建索引的時間長點，能夠查到rebuild index在v$lock中的資訊。（可能有更好方法）

SQL> create index ind_test_object_id on test_object(object_id) tablespace TBS_INDEX1;
Index created.
SQL> alter index ind_test_object_id rebuild;  (在這個過程中，在session 2中查詢v$lock資訊)

同時在session 2中查v$lock的資訊

SQL> @list_lock.sql
SID   Lo LOCK_ID1      Locked Mode      Requested    CTIME      BLOCK
-------- -- -------------------- -------------------------- -------------
15  TM TEST_OBJECT      Share            None                4          0
15  DL 76449             Row-X (SX)        None               4          0

發現鎖的型別為DL和create index時候的鎖型別相同。Document上關於DL鎖型別的說明：
DL Direct loader parallel index create


接下來看一下rebuild index online
Session 1中操作

SQL> alter index ind_test_object_id rebuild online;

(在這個過程中，在session 2中查詢v$lock資訊；其實這裡會阻塞的，如果session 3插入資料早於rebuild online的完成時間，插入資料並沒有提交，這個情況最後再說)
同時Session 2中查詢

SID   Lo LOCK_ID1      Locked Mode      Requested    CTIME      BLOCK
-------- -- -------------------- -------------------------- ---------------
15  TM SYS_JOURNAL_76454    Share        None         1      0
15  DL 76449                     Row-X (SX)    None         3      0
15  TM TEST_OBJECT          Row-S (SS)    None        1          0

完成session2查詢，馬上在Session 3中插入操作

SQL> insert into test_object (select * from dba_objects where rownum<2);
1 row created.

這裡不要提交，session 1中的rebuild index online操作就會hang了。(在這步操作時，rebuild index online還沒有完成，這個情況最後再說，先說session 3中的DML操作不會堵塞)

為什麼在session 3中的不會堵塞呢？？？你沒有發現在v$lock資訊中多了一張表SYS_JOURNAL_76454出來(在metalink有這樣一句話：At the same time it will maintain a journal table for DML data, which has changed during this index rebuilding operation.)


既然這樣，我們就來驗證一下這個過程，用10046事件
Session 1:

SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter index IND_TEST_OBJECT_ID rebuild online;

(這裡會阻塞的，如果session 2不提交)
在session2中插入資料：

SQL> insert into test_object (select * from test_object where rownum<2);
1 row created.
SQL> commit;   (commit後，session1中的rebuild index online繼續)
Commit complete.

在session1操作

SQL> alter session set events '10046 trace name context off';
Session altered.

檢視跟蹤檔案

SQL> !grep SYS_JOURNAL /opt/oracle/admin/dev-db2/udump/dev-db2_ora_24667.trc
create table "TAOBAO"."SYS_JOURNAL_76454" (C0 NUMBER,  opcode char(1), partno number,  rid rowid, primary key( C0 , rid )) organization index TABLESPACE "TBS_INDEX1"
   value="SYS_JOURNAL_76454"
   value="SYS_JOURNAL_76454"
CREATE UNIQUE INDEX "TAOBAO"."SYS_IOT_TOP_76510" on "TAOBAO"."SYS_JOURNAL_76454"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE "TBS_INDEX1" NOPARALLEL
drop table "TAOBAO"."SYS_JOURNAL_76454"
   value="SYS_JOURNAL_76454"
   value="SYS_JOURNAL_76454"
   value="SYS_JOURNAL_76454"

這裡可以發現很多資訊，oracle自動建立了中間表SYS_JOURNAL_76454(索引組織表)，奇怪的一點：我插入完資料，並提交，應該會在update或insert SYS_JOURNAL_76454這個表的。但做了兩遍，結果都沒有發現(知道者，告訴一聲，先謝了)。不知道oracle是怎麼去更新SYS_JOURNAL_76454，來記錄rebuild online index過程中，表(這裡指test_object)被DML操作的資訊。


這裡來看上面rebuild index online被hang住的情況
再來整理一下流程
Session 1:

SQL> alter index ind_test_object_id rebuild online;

(這個過程比較長)
Session 2:

SQL> @list_lock.sql
SID   Lo LOCK_ID1      Locked Mode      Requested    CTIME      BLOCK
-------- -- -------------------- -------------------------- --------------
15  TM SYS_JOURNAL_76454    Share              None         1      0
15  DL 76449                      Row-X (SX)         None         3      0
15  TM TEST_OBJECT          Row-S (SS)          None        1       0

Session 3 :

SQL> insert into test_object (select * from dba_objects where rownum<2);
1 row created.

這裡先不提交commit
再查session 2中:

SID    Lo LOCK_ID1         Locked Mode       Requested    CTIME      BLOCK
-------- -- -------------------- -------------------------- ---------------
12    TM TEST_OBJECT          Row-X (SX)       None          1       1
12    TX USN: 19 RWO: IND_TES Exclusive       None          1        0
15    TX USN: 11 RWO: IND_TES Exclusive       None          2        0
15    TM TEST_OBJECT          Row-S (SS)      Share          5        0
15    DL 76449                Row-X (SX)           None           7        0
15    TM SYS_JOURNAL_76454    Share           None          5       0

發現sid為12的session會阻塞sid為15的(看sid為12的block欄位為1)，因為sid為12的session插入資料後(未提交)，獲得了Exclusive，將導致DDL操作掛起。從測試發現，如果DDL操作到一半，對測試表進行DML操作後(如果允許)，不提交，DDL操作同樣會掛起。

--EOF--

Trackback：http://rdc.taobao.com/blog/dba/html/175_%e4%bb%8e%e9%94%81%e7%9a%84%e8%a7%92%e5%ba%a6%e7%9c%8brebuild-index-online%e5%92%8crebuild-index.html/trackback

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-731431/，如需轉載，請註明出處，否則將追究法律責任。

相關文章