oracle 10g create index online 在基表上的表级锁加锁过程

在Oracle10g上在线创建索引时，会话在基表上的表级锁加锁过程如下：

1、会话在基表上加RS（2）锁。此时基表上已经在运行的DML操作不受影响，新的DML操作也可以开始。

2、会话在取得RS锁后，迅速请求将该锁转换成S（4）锁。由于S锁与RX锁不相容，如果此时基表上有DML操作（已有事务）在运行，创建索引的会话将不得不一直等待直到已有的DML操作完成（回滚或提交）才能取得S锁。在创建索引的会话申请S锁之后开始的DML操作（新事务）将一直处于等待状态，直到创建索引的会话释放S锁为止。表级锁采用的是一种类似队列的处理机制，创建索引的会话申请的S锁排在新事务的RS锁之前，因此即使新事务的RS锁与老事务的RS锁相容，也只能等待S锁处理完成才能获得。如果在创建索引之前基表上有长时间运行的大事务，创建索引的会话需要长时间等待，同时造成新事务也处于长时间等待状态，这对于具有较大压力的OLTP系统来说具有非常大的风险。

3、会话取得S锁后，迅速请求将该锁转换成RS（2）锁，该转换会立即成功。取得RS锁后，会话开始对基表进行全表扫描并创建索引，全表扫描读取的数据是S锁获取成功时刻的数据。此处采用的机制类似于读一致性。而在创建索引过程中，新事务不受影响，新事务的DML操作会记录到JOURNAL表中。

4、会话一直持有RS锁直到基表索引创建完，将JOURNAL表数据合并到新建索引阶段。然后会话请求将锁转换成S（4）锁。同步骤2类似，如果此时基表上有DML操作（已有事务）在运行，创建索引的会话将不得不一直等待直到已有的DML操作完成（回滚或提交）才能取得S锁。而新事务也将处于等待状态，直到S锁释放。会话取得S锁后，将一直持有该锁直到数据合并完成。如果在创建索引的会话请求S锁时，基表上的大事务在运行，会造成新事务长时间等待。同样，如果在索引创建过程中，基表产生了大量DML操作，此时会有大量数据需要合并，这势必会增加数据合并时间，造成会话长时间持有S锁，进而导致新事务长时间等待。此阶段对于具有较大压力的OLTP系统来说风险很大。

下面通过实验测试加锁过程。此处使用的是Saibabu Devabhaktuni（http://sai-oracle.blogspot.com）的测试脚本。

• 在session 1中创建测试表和函数:

scott@ora10g: SQL> create table oib1 as select rownum a, rownum b from dual connect by level <= 101;

Table created.

scott@ora10g: SQL> create or replace function oib_f(a1 in number) return number deterministic as
  2  begin
  3  if (a1 = 100) then
  4  sys.dbms_lock.sleep(120);
  5  end if;
  6  return a1;
  7  end;
  8  /

Function created.

scott@ora10g: SQL> select distinct sid from v$mystat;

       SID
----------
       138

• 另开一个session 2:

scott@ora10g: SQL> select distinct sid from v$mystat;

       SID
----------
       139

scott@ora10g: SQL> set time on
22:34:01 scott@ora10g: SQL> insert into oib1 values (1,1);

1 row created.

22:34:08 scott@ora10g: SQL> select sid, type, id1, id2, lmode, request, block from v$Lock where type='TM';

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       139 TM      17112          0          3          0          0

此时session2的会话139在测试表上持有RX锁。

• 回到session 1:

scott@ora10g: SQL> set time on
22:34:36 scott@ora10g: SQL> alter session set events '10704 trace name context forever, level 12';

Session altered.

设置10704事件来trace创建索引时的加锁情况。

22:34:44 scott@ora10g: SQL> create index oib1_idx on oib1(oib_f(a)) online;

create index语句处于等待状态，直到session 2中的事务完成后。此时如果基表上有新DML操作，新DML操作也将处于等待状态。

• 回到session 2检查表上的加锁情况:

22:35:21 scott@ora10g: SQL> select sid, type, id1, id2, lmode, request, block from v$Lock where type='TM';

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       139 TM      17112          0          3          0          1
       138 TM      17112          0          2          4          0
       138 TM      17114          0          4          0          0

session2（139）持有测试表上的RX锁，而session1（138）持有测试表上的RS锁，同时申请S锁。由于S锁与RX锁不相容，session1处于等待状态。注意object 17114实际上是JOURNAL表。session 1在JOURNAL表上持有S锁。

22:39:31 scott@ora10g: SQL> rollback;

Rollback complete.

等待约5分钟后，回滚insert语句，session 2将释放测试表上的RX锁。

22:39:40 scott@ora10g: SQL> select sid, type, id1, id2, lmode, request, block from v$Lock where type='TM';

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       138 TM      17112          0          2          0          0
       138 TM      17114          0          4          0          0

此时session2（139）已经释放了测试表上的RX锁，session 1（138）在获得S锁后，很快将该锁又转换成RS锁。然后session 1开始创建基表的索引，session1利用一致性读来扫描基表，读取的是基表获得S锁时刻的一致性数据。

• 新开session 3, 在基表上执行DML操作:

scott@ora10g: SQL> select distinct sid from v$mystat;

       SID
----------
       146

scott@ora10g: SQL> set time on
22:40:03 scott@ora10g: SQL> insert into oib1 values (2,2);

1 row created.

在索引开始创建后执行DML操作，此时的DML操作将记入JOURNAL表。在最后阶段合并到基表索引中。

22:40:09 scott@ora10g: SQL> select sid, type, id1, id2, lmode, request, block from v$Lock where type='TM';

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       146 TM      17112          0          3          0          0
       138 TM      17112          0          2          0          0
       138 TM      17114          0          4          0          0

session 1（138）在基表上加RS锁，session 3（146）在基表上加RX锁。在数据合并阶段，session1将申请基表上的S锁。

22:40:18 scott@ora10g: SQL> select object_name,object_type from dba_objects where object_id=17114;

OBJECT_NAME               OBJECT_TYPE
-----------------         -------------
SYS_JOURNAL_17113         TABLE

object 17114是JOURNAL表。

22:42:24 scott@ora10g: SQL> select sid, type, id1, id2, lmode, request, block from v$Lock where type='TM';

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       146 TM      17112          0          3          0          1
       138 TM      17112          0          2          4          0
       138 TM      17114          0          4          0          0

session 1（138）在数据合并阶段对基表请求S锁，处于等待状态。

22:43:56 scott@ora10g: SQL> rollback;

Rollback complete.

等待约几钟后，回滚insert.

22:45:01 scott@ora10g: SQL>

• 回到session 1:

创建索引的操作很快完成。

22:45:01 scott@ora10g: SQL> alter session set events '10704 trace name context off';

Session altered.

scott@ora10g: SQL> select object_name,object_type from dba_objects where object_id=17112;

OBJECT_NAME          OBJECT_TYPE
-------------------- --------------------
OIB1                 TABLE

object 17112是测试表OIB1.

scott@ora10g: SQL> select to_char(17112,'xxxxx') from dual;

TO_CHA
------
  42d8

把17112转换成16进制数。
 

以下是trace文件中的信息。
*** 2012-06-15 22:34:51.633
ksqgtl *** DL-000042d8-00000000 mode=3 flags=0x11 timeout=0 ***  －－＞先请求模式为3类型的DL锁
ksqgtl: xcb=0x38f85e518, ktcdix=2147483647, topxcb=0x38f85e518
        ktcipt(topxcb)=0x0
*** 2012-06-15 22:34:51.633
ksucti: init txn DID from session DID 0001-0017-0000000B
ksqgtl:
        ksqlkdid: 0001-0017-0000000B
*** 2012-06-15 22:34:51.634
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0017-0000000B
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-0000000B
ksqgtl: RETURNS 0      －－＞请求很快完成
*** 2012-06-15 22:34:51.634
ksqgtl *** DL-000042d8-00000000 mode=3 flags=0x11 timeout=0 *** －－＞再请求模式为3类型的DL锁
ksqgtl: xcb=0x38f85e518, ktcdix=2147483647, topxcb=0x38f85e518
        ktcipt(topxcb)=0x0
*** 2012-06-15 22:34:51.634
ksucti: init session DID from txn DID: 0001-0017-0000000B
ksqgtl:
        ksqlkdid: 0001-0017-0000000B
*** 2012-06-15 22:34:51.634
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0017-0000000B
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-0000000B
*** 2012-06-15 22:34:51.634
ksqcmi: DL,42d8,0 mode=3 timeout=0
ksqcmi: returns 0
ksqgtl: RETURNS 0   －－＞请求很快完成

*** 2012-06-15 22:34:51.634
ksqgtl *** TM-000042d8-00000000 mode=2 flags=0x401 timeout=21474836 ***  －－＞在基表上请求RS锁
ksqgtl: xcb=0x38f85e518, ktcdix=2147483647, topxcb=0x38f85e518
        ktcipt(topxcb)=0x0
*** 2012-06-15 22:34:51.634
ksucti: init session DID from txn DID: 0001-0017-0000000B
ksqgtl:
        ksqlkdid: 0001-0017-0000000B
*** 2012-06-15 22:34:51.634
*** ksudidTrace: ksqgtl
        ktcmydid(): 0001-0017-0000000B
        ksusesdi:   0000-0000-00000000
        ksusetxn:   0001-0017-0000000B
*** 2012-06-15 22:34:51.634
ksqcmi: TM,42d8,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqgtl: RETURNS 0 －－＞请求很快完成，获得RS锁
......
*** 2012-06-15 22:34:51.662 －－＞22:34:51.662－22:34:51.634 ＝0.028秒
ksqcnv: TM-000042d8,00000000 mode=4 timeout=21474836 －－＞很快请求将RS锁convert成S锁

*** 2012-06-15 22:34:51.662
ksqcmi: TM,42d8,0 mode=4 timeout=21474836
*** 2012-06-15 22:39:40.694 －－＞convert花费了约5分钟（22:39:40.694 -22:34:51.662）才完成。这是因为session 2持有RX锁，
ksqcmi: returns 0                    －－＞session1 convert一直等待。session2 rollback后，session 1才conver完成。
ksqcnv: RETURNS 0 －－＞convert成功
*** 2012-06-15 22:39:40.694
ksqcnv: TM-000042d8,00000000 mode=2 timeout=21474836  －－＞获得S锁后，立即（22:39:40.694-22:39:40.694）请求将S锁
*** 2012-06-15 22:39:40.694                                              －－＞convert成RS锁，并且covert立即成功。
ksqcmi: TM,42d8,0 mode=2 timeout=21474836
ksqcmi: returns 0
ksqcnv: RETURNS 0                                                              －－＞convert成功，开始创建索引
......
*** 2012-06-15 22:41:40.704 －－＞22:41:40.704－22:39:40.694＝2分钟
ksqcnv: TM-000042d8,00000000 mode=4 timeout=21474836 －－＞约2分钟后（函数sleep时间，表数据量很小，索引创建时间可忽略）
*** 2012-06-15 22:41:40.704 －－＞请求将RS锁convert成S锁
ksqcmi: TM,42d8,0 mode=4 timeout=21474836
*** 2012-06-15 22:45:01.046      －－>约3分钟后（22:45:01.046－22:41:40.704），才convert成功。因为session3持有RX锁，
ksqcmi: returns 0 －－>session1一直处于等待状态。session3回滚后，session1才convert成功。
ksqcnv: RETURNS 0                   －－>convert成功。
......
*** 2012-06-15 22:45:01.062   
ksqrcl: DL,42d8,0                        －－＞应该是release DL锁
ksqrcl: returns 0
*** 2012-06-15 22:45:01.062
ksqrcl: DL,42d8,0                        －－＞再release DL锁
ksqrcl: returns 0
......
*** 2012-06-15 22:45:01.080
ksqrcl: TM,42d8,0                      －－＞release TM锁。从取得S锁到releasse锁的时间很短（22:45:01.080 －22:45:01.046）
ksqrcl: returns 0 －－＞这个时间应该是与需要merge的数据量直接相关。

在Oracle10g中rebuild index online与create index online的操作过程一样，加锁过程也一样，都是通过全表扫描来建立索引。

以下是用explain plan看到的rebuild index online执行计划：

scott@ora10g: SQL> explain plan for alter index OIB1_IDX rebuild online;

Explained.
scott@ora10g: SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2031255143

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | ALTER INDEX STATEMENT  |          |   101 |   808 |     3   (0)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| OIB1_IDX |       |       |            |          |
|   2 |   SORT CREATE INDEX    |          |   101 |   808 |            |          |
|   3 |    TABLE ACCESS FULL   | OIB1     |   101 |   808 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------