创建索引(B*tree)时对表的加锁方式

创建一个测试表

SQL> create table test as select * from dba_objects;

Table created

由于test表很小，创建索引时间很短暂，所以我创建一个系统触发器来记录创建索引时在基表上的LOCK，这里我不想对session进行跟踪。

创建一个记录表，用来记录创建索引时产生的LOCK

SQL> create table lock_obj (username varchar2(100),sid number,lock_type varchar2(100),
2 object_name varchar2(100),XIDUSN number,XIDSLOT number,XIDSQN number);

Table created

创建一个系统触发器(注意，由于该触发器需要读取v$locked_object,dba_objects，所以请在创建该触发器的时候授予该用户 select any dictionary 权限，或者授予 select on v_$locked_object 等等权限，直接授予该用户DBA权限是不行的哦，因为触发器需要基表直接授权。)

SQL> create or replace trigger t_create_index
2 before create on schema
3 begin
4 insert into lock_obj
5 select oracle_username username,session_id sid,decode(
6 locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',
7 5,'Share Row Exclusive',6,'Exlusive') lock_type,object_name,xidusn,xidslot,xidsqn
8 from v$locked_object,dba_objects
9 where v$locked_object.object_id=dba_objects.object_id;
10 end t_create_index;
11 /

Trigger created

开始试验:

SQL> create unique index i_object_id on test(object_id);

Index created

SQL> select * from lock_obj;

USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- --- --------------- --------------- ---------- ---------- ----------
ROBINSON 149 Share TEST 0 0 0
ROBINSON 149 Row Exclusive LOCK_OBJ 0 0 0

可以看到 在创建索引的时候会在基表test上面加上一个Share lock，加上这个Share lock 有什么作用呢？对表加上Share lock之后，我们就无法对表进行DML操作

SQL> lock table test in share mode;

Table(s) locked

另外打开一个session ，对test表进行DML操作

SQL> delete from test where rownum=1;

此时删除时不会成功的，必须等到前一个session commit 或者是rollback才能够成功。

删除刚才创建的索引，使用online 方式创建索引

SQL> drop index i_object_id ;

Index dropped

SQL> delete from lock_obj;

2 rows deleted

SQL> commit;

Commit complete

以online 方式创建索引

SQL> select * from lock_obj;

USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- --- --------------- -------------------- ---------- ---------- ----------
ROBINSON 149 Row Exclusive OBJ$ 5 8 347
ROBINSON 149 Share SYS_JOURNAL_51475 5 8 347
ROBINSON 149 Row share TEST 0 0 0
ROBINSON 149 Row Exclusive LOCK_OBJ 0 0 0
ROBINSON 149 Row Exclusive OBJ$ 5 8 347
ROBINSON 149 Row share TEST 5 47 346
ROBINSON 149 Row Exclusive LOCK_OBJ 5 47 346
ROBINSON 149 Row Exclusive LOCK_OBJ 5 8 347
ROBINSON 149 Row share TEST 5 47 346
ROBINSON 149 Row Exclusive LOCK_OBJ 5 47 346
ROBINSON 149 Row Exclusive LOCK_OBJ 5 8 347

可以看到，以online方式创建索引，对基表test只会加上 Row share lock，这个时候是可以进行dml操作的

SQL> lock table test in row share mode;

Table(s) Locked.

另外打开一个session

SQL> delete from test where rownum=1;

1 row deleted

SQL> rollback;

Rollback complete

由此证明以 online 方式创建索引不会影响基表表的DML操作，同理，对索引rebuild的时候，以online的方式也不会影响对基表的DML操作。实验到这里没完，大家请注意看，以online方式创建索引还会对一个 叫SYS_JOURNAL_51475的表加上Share lock。而且还会对OBJ$加上Row Exclusivelock。

SQL> select object_id,object_type from dba_objects where object_name='SYS_JOURNAL_51475';

OBJECT_ID OBJECT_TYPE
---------- -------------------

奇怪，为什么没有发现 这个OBJECT呢？我另外创建一个索引 看看这个表是否还会出现

SQL> delete from lock_obj;

11 rows deleted

SQL> create index i_d_object_id on test(data_object_id) online;

Index created

SQL> select * from lock_obj;

USERNAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- --- --------------- -------------------- ---------- ---------- ----------
ROBINSON 149 Row Exclusive OBJ$ 5 31 348
ROBINSON 149 Share SYS_JOURNAL_51478 5 31 348
ROBINSON 149 Row share TEST 5 28 348
ROBINSON 146 Row share TEST 0 0 0
ROBINSON 149 Row Exclusive LOCK_OBJ 5 28 348
ROBINSON 149 Row Exclusive LOCK_OBJ 5 31 348
ROBINSON 149 Row share TEST 0 0 0
ROBINSON 146 Row share TEST 0 0 0
ROBINSON 149 Row Exclusive LOCK_OBJ 0 0 0
ROBINSON 149 Row Exclusive OBJ$ 5 31 348
ROBINSON 149 Row share TEST 5 28 348
ROBINSON 146 Row share TEST 0 0 0
ROBINSON 149 Row Exclusive LOCK_OBJ 5 28 348
ROBINSON 149 Row Exclusive LOCK_OBJ 5 31 348

14 rows selected
这次居然变成了51478

SQL> select object_id,object_type from dba_objects where object_name LIKE '%SYS_JOURNAL%';

OBJECT_ID OBJECT_TYPE
---------- -------------------

恩，直接查数据字典是查不到的，看来需要对session进行跟踪一下了

SQL> delete from lock_obj;

14 rows deleted

SQL> commit;

Commit complete

SQL> alter trigger t_create_index disable;

Trigger altered

2010-03-17更新 续：

对于 SYS_JOURNAL_的研究使用SQL_TRACE跟踪

SQL> select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);

SID SERIAL#
---------- ----------
148 16
另外打开一个session对其进行跟踪

SQL> exec dbms_system.set_sql_trace_in_session(148,16,true);

PL/SQL procedure successfully completed.

SQL> create index i_object_id on test(object_id) online;

Index created.
SQL> exec dbms_system.set_sql_trace_in_session(148,16,false);

PL/SQL procedure successfully completed.

部分的trace 文件 ，我只摘取 SYS_JOURNAL 部分
create table "ROBINSON"."SYS_JOURNAL_51683" (C0 NUMBER, opcode char(1),
partno number, rid rowid, primary key( C0 , rid )) organization index
TABLESPACE "ROBINSON"

.......................................................................

CREATE UNIQUE INDEX "ROBINSON"."SYS_IOT_TOP_51684" on
"ROBINSON"."SYS_JOURNAL_51683"("C0","RID") INDEX ONLY TOPLEVEL TABLESPACE
"ROBINSON" NOPARALLEL

.......................................................................

drop table "ROBINSON"."SYS_JOURNAL_51683" purge

.......................................................................

SELECT topology
FROM
SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b WHERE b.owner =
'ROBINSON' AND b.table_name = 'SYS_JOURNAL_51683'

从SQL TRACE跟踪文件可以看出 表SYS_JOURNAL_是一个临时的表,用来存放临时数据，这样就不会造成对原表进行share lock了奶奶的，暂时就只能 研究到这里了，内部的机制确实有点复杂