rebuildindexonline的锁机制浅析.doc

一般都说，rebuild index online不阻塞DML操作，这是相对于rebuild index来说的，加上了online，只是在rebuild的期间不阻塞DML，但是在开始和结束阶段还是可能阻塞其他进程的DML的，要弄清楚到底是 阻塞还是不阻塞，何处阻塞，为什么阻塞，还是要从锁的角度来分析。本文实验环境为Oracle 10.2.0.4。
Oracle中的锁，一共有6两种模式：
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive
我 们知道，DML操作一般要加两个锁，一个是对表加模式为3的TM锁，一个是对数据行的模式为6的TX锁。只要操作的不是同一行数据，是互不阻塞的。但是 rebuild index online在开始和结束的时候是需要对表加一个模式为4的TM锁的，这个可以很容易通过实验观察到，实验中的测试表t是通过create table t as select * from all_objects生成，并且多次执行insert into t select * from t产生较多的数据，以便延迟rebuild的时间来观察系统中锁的情况：
session 1：
SQL> delete from t where object_id=28;

1 row deleted.
session 2：
SQL> alter index ix_t rebuild online;
Session 2被阻塞，会话挂起，这时查询v$lock，可以得到如下结果：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1622 TX 655398 1361 6 0
从 上面的结果可以知道，1622是session 1，1643是session 2，session 2一共出现了4个锁，两个DL锁，一个针对表t的TM锁，一个是online rebuild index时需要的一个中间表的TM锁，中间表用于记录rebuild期间的增量数据，原理类似于物化视图日志，其object_id为10607，这是 一个索引组织表(IOT)，从这里我们也可以发现IOT的优点和适合的场合，这张中间表只有插入，不会有删除和修改操作，而且只有主键条件查询，正是 IOT最合适的场景：
SQL> select object_name,object_type from all_objects where object_id=10607;

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_JOURNAL_10602 TABLE

SQL> select table_name,iot_type from all_tables where table_name='SYS_JOURNAL_10602';

TABLE_NAME IOT_TYPE
------------------------------ ------------
SYS_JOURNAL_10602 IOT
Session 2在请求一个模式为4的TM锁，模式4会阻塞这个表上的所有DML操作，所以这是再往这个表上执行DML也会挂起
session 3：
SQL> delete from t where object_id=46;

SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 0 3
1622 TX 655398 1361 6 0
1627 就是session 3，请求模式为3的TM锁无法获得，会话被阻塞。这是因为锁请求是需要排队的，即使session 3和session 1是可以并发的，但由于session 2先请求锁并进入等待队列，或来的session 3也只好进入队列等待。所以如果在执行rebuild index online前长事务，并且并发量比较大，则一旦执行alter index rebuild online，可能因为长事务阻塞，可能导致系统瞬间出现大量的锁，对于压力比较大的系统，这是一个不小的风险。这是需要迅速找出导致阻塞的会话kill 掉，rebuild index online一旦执行，不可轻易中断，否则可能遇到ORA-08104。
在session 1执行rollback，可以发现很短时间内session 3也正常执行完毕，说明session 2只有模式4的TM锁的时间很短，然后在rebuild online的进行过程中，对表加的是模式为2的TM锁，所以这段时间不会阻塞DML操作：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1643 TM 10599 0 2 0
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1627 TX 655392 1361 6 0
保持session 3的事务不提交，等待一段时间后，session 2始终无法完成操作，再观察系统中锁的情况，可以发现又发生了变化：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1643 TX 589852 258 6 0
1627 TX 655392 1361 6 0
Session 2又开始在请求模式4的TM锁，被session 3阻塞!这是在session 1再执行DML操作，同样会被session 2阻塞，进入锁等待队列。
Session 1：
SQL>delete from t where object_id=11;

SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
1643 DL 10599 0 3 0
1643 DL 10599 0 3 0
1622 TM 10599 0 0 3
1643 TM 10599 0 2 4
1643 TM 10607 0 4 0
1627 TM 10599 0 3 0
1643 TX 589852 258 6 0
1627 TX 655392 1361 6 0
在session 3执行rollback或者commit以后，session 2和session 3都很快执行完毕。
从 上面的试验可以发现，虽然rebuild index online在执行期间只持有模式2的TM锁，不会阻塞DML操作，但在操作的开始和结束阶段，是需要短暂的持有模式为4的TM锁的，这段会阻塞表上的所 有DML操作。我们在做rebuild index online的时候，一定要在开始和结束阶段观察系统中是否有长事务的存储，对于并发量较大的系统，最严重的后果，可能在这两个关键点导致数据库产生大量 锁等待，系统负载飙升，甚至宕机。

rebuild index online的锁机制浅析
[收藏此页] [打印]
作者：IT  2008-10-15
内容导航：
在Oracle11g中执行rebuild index online
第1页： 在Oracle10g中执行rebuild index online 第2页： 在Oracle11g中执行rebuild index online
文本Tag: Oracle
到了Oracle11g，这种情况有所变化，还是通过同样的实验来观察一下Oracle11g到底做出了怎样的改进，对于DBA来说又有怎样的好处。实验环境为Oracle11.1.0.6。
session 1：
SQL> delete from t where object_id=28;

1 row deleted.
session 2：
SQL> alter index ix_t rebuild online;
session 2同样被挂起，查看v$lock：
SQL> select sid,type,id1,id2,lmode,request from v$lock where type in('DL','TM','TX');

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TX 458781 377 0 4
170 TM 13596 0 3 0
137 TM 13596 0 2 0
137 TM 13599 0 4 0
170 TX 458781 377 6 0
137 TX 524304 402 6 0
其 中170为session 1，137为session 2。可以看到session 2正在请求一个模式为4的TX锁，注意和Oracle10.2.0.4请求的TM锁是不一样的，而且在我们以前的概念中，TX锁的模式都是6，这里出现了 模式4的TX锁请求，应该是Oracle11g中新引入的。那么模式4的TX锁和TM锁有什么不同呢？我们继续前面的实验步骤：
session 3：
SQL> delete from t where object_id=46;

1 row deleted.
session 3的DML操作顺利完成，没有被阻塞。而在10g当中，session 3是会被session 2请求的TM锁所阻塞的，这一点改进是非常有意思的，这样即使rebuid online操作被session 1的长事务阻塞，其他会话的DML操作，只要不和session 1冲突，都可以继续操作，在Oracle10g及以前版本中的执行rebuild index online而造成锁等待的风险被大大的降低了。
接下来在session 1执行rollback，观察rebuild index online执行期间的锁的情况，136是session 3：
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TM 13596 0 2 0
137 TM 13599 0 4 0
136 TM 13596 0 3 0
136 TX 327684 414 6 0
137 TX 524304 402 6 0
137 TX 524321 402 6 0
等待一段时间，rebuild index online临近结束，再次观察锁的情况：
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
137 DL 13596 0 3 0
137 DL 13596 0 3 0
137 TX 327684 414 0 4
137 TM 13596 0 2 0
137 TM 13599 0 4 0
136 TM 13596 0 3 0
136 TX 327684 414 6 0
137 TX 524304 402 6 0
可 以看到session 2又在请求一个模式为4的TX锁，同样的，这个锁也不会阻塞其他的DML。由于session 3的事务没有提交，session 2被阻塞，这时再将session 3执行提交或者rollback，则session 2的rebuild立即完成。
Oracle11g在很多细节方面确实做了不少的优化，而且像这样的优化，对于提高系统的高可用性的好处是不 言而喻的，在Oracle11g中，执行rebuild index online的风险将比10g以及更老版本中小得多，因为从头至尾都不再阻塞DML操作了，终于可以算得上名副其实的online操作了。