【罗玄】从锁的角度看rebuild index online和rebuild index

众所周知，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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9390331/viewspace-731431/