转载

create index...online操作过程中会申请持有哪些锁

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值

正文到此结束