11g下，在給表建立索引時如果加上online選項，不會阻塞同時進行的DML操作，相當給力的一個功能。
與不帶online的索引建立方式相比在鎖的申請與持有機制上有何區別，我們來比較一下

###建立測試表
sqlplus ad/Uiop246!
create table t0528_1 as select * from all_users;

select object_id from dba_objects where object_name='T0528_1';

OBJECT_ID
----------
17177

---session 1: update但不提交
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
27 0 0

update t0528_1 set username=dbms_random.string('u',5) where user_id=0;

---session 2: create index(非online方式)
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
100 0 0

create index ind_uname on t0528_1(username) tablespace ts_pub; <---直接報錯退出

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

alter session set ddl_lock_timeout=60; <---設定ddl timeout為60s，以便觀察到後面create index時請求的鎖模式

create index ind_uname on t0528_1(username); <---操作被掛起

---session 3: 觀察session 1持有的鎖、session 2請求的鎖
select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100) order by sid;

SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
27 17177 0 TM 3 0 <---持有lockmode=3的鎖，即型別為row-X (SX)的鎖
27 100 0 AE 4 0
27 65566 340 TX 6 0
100 100 0 AE 4 0
100 17177 0 TM 0 4 <---請求lockmode=4的鎖，即型別為share (S)的鎖

以上資訊可以看出，針對t0528_1(object_id=17177)表，在session 1已經持有了SX鎖的情況下，session 2請求S鎖，由於S與SX鎖不相容，所以session 2會遇到ORA-00054

等待session 2超時後改用online方式create index
---session 2: create index ... online
alter session set ddl_lock_timeout=0; <---復位ddl timeout為0

create index ind_uname on t0528_1(username) online; <----操作掛起，等待session 1的事務結束

---session 3：觀察session 1、session 2上持有的鎖
select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100) order by sid;
SID ID1 ID2 TY LMODE REQUEST
---------- ---------- ---------- -- ---------- ----------
27 100 0 AE 4 0
27 17177 0 TM 3 0 <---依舊持有lockmode=3的鎖，即型別為row-X (SX)的鎖
27 65566 340 TX 6 0
100 100 0 AE 4 0
100 17177 0 TM 2 0 <---轉而持有lockmode=2的鎖，即型別為row-S (SS)的鎖
100 17179 0 TM 4 0 <---object_id=17179指向新增的IOT表SYS_JOURNAL_17178
100 458752 331 TX 6 0
100 17177 0 OD 4 0 <---online模式下才有的OD型別的鎖，它代表online ddl
100 17177 0 DL 3 0 <---online模式下才有的DL型別的鎖，它代表direct loader index creation
100 17177 0 DL 3 0
100 65566 340 TX 0 4 <---在事務級請求持有share (S)鎖，需等待session 1持有的exclusive (X)級的事務鎖釋放，才能申請成功

col type format a5
col name format a40
col description format a60
set linesize 130
select type,name,description from v$lock_type where type in ('OD','DL');

TYPE NAME DESCRIPTION
----- ---------------------------------------- ------------------------------------------------------------
DL Direct Loader Index Creation Lock to prevent index DDL during direct load
OD Online DDLs Lock to prevent concurrent online DDLs

SQL> col object_name format a30
SQL> set linesize 100
SQL> select owner,object_name,object_id from dba_objects where object_id=17179

OWNER OBJECT_NAME OBJECT_ID
------------------------------ ------------------------------ ----------
SYS SYS_JOURNAL_17178 17179

SQL> select owner,table_name,iot_type from dba_tables where table_name='SYS_JOURNAL_17178';

OWNER TABLE_NAME IOT_TYPE
------------------------------ ------------------------------ ------------
SYS SYS_JOURNAL_17178 IOT


和前一次create index情況下持有及請求的鎖資源相比，create index ... online方式有以下一些改變：
(1) 過程中會持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)兩種型別的鎖
(2) 表級鎖TM的持有模式為row-S (SS)，與row-X (SX)型別的鎖互相相容，因此不會在表級發生阻塞
(3) 阻塞發生在行級鎖申請階段，即請求的share (S)型別的鎖與執行DML的session已經持有的exclusive (X)鎖之間存在不相容的情況；相比非online方式的表級鎖，鎖的粒度上更加細化，副作用更小
(4) 新增以SYS_JOURNAL_為字首的IOT表，記錄與索引建立動作同時進行的其它DML操作修改過的記錄，等到索引建立完成前將IOT表裡的記錄合併至索引中

session 2等待期間如果再開一個session對t0528_1表進行dml操作，這個操作依然會成功
---session 4：insert into ...
select * from v$mystat where rownum=1;
SID STATISTIC# VALUE
---------- ---------- ----------
22 0 0

insert into t0528_1 values('AAA',999,to_Date('20160528','yyyymmdd')); <---注意這裡並沒有commit

---session 3：觀察session 1、session 2、session 4上持有和請求的鎖
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (27,100,22) order by sid;

SID ID1 ID2 TYPE LMODE REQUEST
---------- ---------- ---------- ----- ---------- ----------
22 262163 345 TX 6 0
22 100 0 AE 4 0
22 17177 0 TM 3 0
27 65566 340 TX 6 0
27 17177 0 TM 3 0
27 100 0 AE 4 0
100 458752 331 TX 6 0
100 17179 0 TM 4 0
100 17177 0 TM 2 0
100 100 0 AE 4 0
100 17177 0 OD 4 0
100 17177 0 DL 3 0
100 17177 0 DL 3 0
100 65566 340 TX 0 4

現有的等待鏈有兩組：session 1(update)->session 2(create index online)和session 4(insert)->session 2(create index online)，可以看出並不因為session 4的insert比session 2的create index ... online晚發起而出現前者被後者阻塞的情況，所以create index online線上建立索引的方式對於DML操作不會產生干擾，但是如果併發的DML操作很多，會增加索引建立的耗時

消除這個等待鏈，只需分別在session 1、session 4執行commit
---session 1
commit;

---session 4
commit;

索引建立成功， SYS_JOURNAL_為字首的IOT表也已被清理
col table_name format a20
col column_name format a40
col index_name format a30
set linesize 130
select table_name,column_name,index_name from dba_ind_columns where table_name='T0528_1'

TABLE_NAME COLUMN_NAME INDEX_NAME
-------------------- ---------------------------------------- ------------------------------
T0528_1 USERNAME IND_UNAME

SQL> select * from sys.SYS_JOURNAL_17178;
select * from sys.SYS_JOURNAL_17178
*
ERROR at line 1:
ORA-00942: table or view does not exist

由於我們測試表過小所以create index很快結束，沒能觀察到SYS_JOURNAL_字首的表到底存放了哪些內容，下面再補充一個小測試
create table t0528_2 as select rownum rn,t.* from dba_tables t connect by level<3;

create index ind_rn on t0528_2(rn) online tablespace ts_pub;

在create index尚在執行時另開一session執行
update t0528_2 set rn=99999 where rn=1;
commit;

檢查IOT表
SELECT * FROM AD.SYS_JOURNAL_9845625;
C0 O PARTNO RID
---------- - ---------- ------------------
99999 I 0 D/////ANYAAC2CTAAA
1 D 0 D/////ANYAAC2CTAAA

存放形式有點類似與MV log，舊值1被標記為Delete，新值99999標記為Insert，唯一不同的是RID列記錄的並非是完整的rowid值

create index...online操作過程中會申請持有哪些鎖

相關文章