oracle ae锁表,append提示锁表情况

数据库版本 oracle 11.2.0.3.5

hint提示中，添加append提示，可以绕过数据缓冲区，直接加载到数据段最后，可提供加载速度。但在实际使用过程中，发现一些问题

情况1：

insert /*+append*/ into tab1 values (......)情况下，添加了append提示，对tab1表会加上3级TM锁，此时不影响该表上其他DML事物

情况2:

insert /*+append*/ into tab2 select * from tab1情况下，添加了append提示，会对tab2表加上6级TM锁，此时该表上的其他DML事物会被全部阻塞

建立基础表

tab1

create table tab1

(

a varchar(255),

b varchar(255),

c varchar(255),

d varchar(255),

e varchar(255),

f varchar(255),

g varchar(255),

h varchar(255),

i varchar(255),

j varchar(255),

k varchar(255),

l varchar(255),

m varchar(255),

n varchar(255),

o varchar(255),

p varchar(255),

q varchar(255),

r varchar(255),

s varchar(255),

t varchar(255),

u varchar(255),

v varchar(255),

w varchar(255),

x varchar(255),

y varchar(255),

z varchar(255)

);

tab2create table tab2

(

a varchar(255),

b varchar(255),

c varchar(255),

d varchar(255),

e varchar(255),

f varchar(255),

g varchar(255),

h varchar(255),

i varchar(255),

j varchar(255),

k varchar(255),

l varchar(255),

m varchar(255),

n varchar(255),

o varchar(255),

p varchar(255),

q varchar(255),

r varchar(255),

s varchar(255),

t varchar(255),

u varchar(255),

v varchar(255),

w varchar(255),

x varchar(255),

y varchar(255),

z varchar(255)

);

情况1测试：

查看session的sidSQL> select USERENV('sid') from dual;

USERENV('SID')

--------------

250

执行insert语句：begin

for x in 1..500000 loop

insert /*+append*/ into tab1 values (x,'aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa','aaaaaaaaaaaaaaa');

end loop;

commit;

end;

/

检查锁资源SQL> select * from v$lock where sid=250;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000A840A890 00000000A840A8E8 250 AE 100 0 4 0 4607 0

00007FE7733A6778 00007FE7733A67D8 250 TM 14235 0 3 0 5 0

00000000A6856EC8 00000000A6856F40 250 TX 131081 5259 6 0 5 0

情况2测试：

同样的session执行语句insert /*+append*/ into tab2 select * from tab1;

检查锁资源SQL> select * from v$lock where sid=250;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000A840A890 00000000A840A8E8 250 AE 100 0 4 0 4727 0

00007FE7733A6778 00007FE7733A67D8 250 TM 14236 0 6 0 2 0

00000000A68B0208 00000000A68B0280 250 TX 327690 5264 6 0 2 0

情况3测试

去除append提示后insert into tab2 select * from tab1;

检查锁资源SQL> select * from v$lock where sid=250;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

00000000A840A890 00000000A840A8E8 250 AE 100 0 4 0 5047 0

00007FE7733A50E0 00007FE7733A5140 250 TM 14236 0 3 0 2 0

00000000A68B0208 00000000A68B0280 250 TX 589848 5326 6 0 2 0

结论：

insert into tab1 select * from tab2这类语句，添加/*+append*/提示，会造成6级TM锁，阻塞tab1上其他非查询类DML操作，究竟为什么会这样，将继续研究。