ITPub博客

首页 > 数据库 > 数据库开发技术 > create index时对表加的什么锁

create index时对表加的什么锁

原创 数据库开发技术 作者:waityou81 时间:2007-01-02 13:04:10 0 删除 编辑
create index时对表加的什么锁[@more@]

SQL> create index idx_1 on A02 (owner, object_name, subobject_name);

Index created

SQL> select sid from v$mystat where rownum<2;

SID
----------
16

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
TEST 16 Share A02 7 30 84
TEST 16 Row Exclusive OBJ$ 7 30 84

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
16 TX 458782 84 Exclusive 0 6 0
16 TM 18 0 Row Exclusive 0 6 0
16 TM 6319 0 Share 0 6 0

select owner,object_name,object_id from all_objects where object_id in (18,6319)
TEST A02 6319
SYS OBJ$ 18

可见在create index时会在A02表上加TM为Share的锁,在sys.obj$上加TM为Row Exclusive的锁,TX为Exclusive的锁。
如果在create index时A02上有DML,会首先在A02上加TM为Row Exclusive的锁,其与存在在A02上的TM为Share的锁不相容,所以会发生等待。
insert into a02 select * from a02 where rownum<2(SID=15)

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
TEST 15 None A02 0 0 0
TEST 16 Share A02 7 30 84
TEST 16 Row Exclusive OBJ$ 7 30 84

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
15 TM 6319 0 None 3 4 0
16 TX 458782 84 Exclusive 0 6 0
16 TM 18 0 Row Exclusive 0 6 0
16 TM 6319 0 Share 0 6 1

同样,如果TABLE上有DML操作,这时进行CREATE INDEX,会报错ORA-00054: resource busy and acquire with NOWAIT specified。
因为dml会在A02上加TM为Row Exclusive的锁,会阻塞create index时对A02加TM为Share的锁。

那为什么create index online不会阻塞dml呢?

SQL> drop index idx_1;

Index dropped

SQL> create index idx_1 on A02 (owner, object_name, subobject_name) online;

select rpad(oracle_username, 10) o_name,
session_id sid,
decode(locked_mode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
object_name,
xidusn,
xidslot,
xidsqn
from v$locked_object, all_objects
where v$locked_object.object_id = all_objects.object_id;

TEST 16 Row share A02 1 23 89
TEST 16 Share SYS_JOURNAL_6391 1 23 89

select sid,
type,
id1,
id2,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive') lock_type,
request,
ctime,
block
from v$lock
where TYPE IN ('TX', 'TM');

16 TX 65559 89 Exclusive 0 4 0
16 TM 6392 0 Share 0 4 0
16 TM 6319 0 Row share 0 4 0

可见在create index online时对A02加的是TM为Row share的锁,其与Row Exclusive是兼容的。同时对sys.obj$的锁消失。会产生一个SYS_JOURNAL_6391的表,用于create index online时DML操作产生的索引维护。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/115194/viewspace-887969/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    4
  • 访问量
    168823