rebuild index online的锁机制浅析

一般都说,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的时候,一定要在开始和结束阶段观察系统中是否有长事务的存储,对于并发量较大的系统,最严重的后果,可能在这两个关键点导致数据库产生大量锁等待,系统负载飙升,甚至宕机。



无觅相关文章插件,快速提升流量

2条评论

  • […] 上一篇:rebuild index online的锁机制浅析 […]

    • At 2012.06.15 18:23, saup007 said:

      在11.2.0.2试了一下,在online之前有dml,不会阻塞,但之前那个dml还不提交,online操作也会被阻塞的
      sid=29 为create index online的操作

      SQL> select * from v$lock where sid in (29,32) order by sid;

      ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
      —————- —————- ———- —- ———- ———- ———- ———- ———- ———-
      000000007E0C45F8 000000007E0C4650 29 DL 83677 0 3 0 55 0
      000000007E0C4DD8 000000007E0C4E30 29 AE 100 0 4 0 21790 0
      000000007E0C64C8 000000007E0C6520 29 OD 83677 0 4 0 55 0
      000000007D35F1F0 000000007D35F268 29 TX 393219 1536 6 0 55 0
      00002BA658CEC190 00002BA658CEC1F0 29 TM 83685 0 4 0 55 0
      000000007E0C5F88 000000007E0C5FE0 29 TO 66944 1 3 0 148 0
      000000007E0C6308 000000007E0C6360 29 DL 83677 0 3 0 55 0
      000000007E0C65A8 000000007E0C6600 29 TX 589853 1290 0 4 55 0
      00002BA658CEC190 00002BA658CEC1F0 29 TM 83677 0 2 0 55 0
      00002BA658CEC190 00002BA658CEC1F0 32 TM 83677 0 3 0 97 0
      000000007D37CEF0 000000007D37CF68 32 TX 589853 1290 6 0 97 1
      000000007E0C5EA8 000000007E0C5F00 32 AE 100 0 4 0 22299 0


      (Required)
      (Required, will not be published)