Online Contents

09. tm_contention

----------------------------------------------
-- @name: tm_contention
-- @author: dion cho
-- @description: test for tm contention
----------------------------------------------

@capture_on

@version

-------------------------------------
-- index create(offline)
drop table t2 purge;
drop table t1 purge;

create table t1(c1 char(100));

insert into t1
select level
from dual
connect by level <= 500000
;

commit;

-- session#1
create index t1_n1 on t1(c1);

-- session#2
insert into t1 values('1');
commit;

-- session#3
@wait

@lock_tree



-------------------------------------
-- index create(online)
-- session#1
insert into t1 values('2');

-- session#2
alter index t1_n1 rebuild online
;


-- session#3
insert into t1 values('3');

-- session#1
@wait

@lock_tree


-------------------------------------
-- direct path insert
drop table t2 purge;
drop table t1 purge;

create table t1(c1 int, c2 int);

-- session#1
insert /*+ append */ into t1
select level, level
from dual
connect by level <= 1000
;


-- session#2
insert into t1 values(1, 1);

-- session#1
@wait

@lock_tree



--------------------------------------
-- by foreign key
drop table t2 purge;
drop table t1 purge;

create table t1(c1 int, c2 int);

alter table t1
  add constraint t1_pk primary key(c1)
;

create table t2(c1 int, c2 int);

alter table t2
  add constraint t2_fk foreign key(c1)
    referencing t1(c1)
;


insert into t1
select level, level
from dual
connect by level <= 10
;

insert into t2
select level, level
from dual
connect by level <= 10
;

commit;


-- check without vs. with index


-- without index

-- session#1
insert into t2 values(1, 1);


-- session#2
delete from t2 where c1 = 2;

delete from t1 where c1 = 2;

-- session#1
@wait

@lock_tree


-- with index
-- session#1
rollback;

-- session#2
rollback;

create index t2_n1 on t2(c1);

-- session#1
insert into t2 values(1, 1);


-- session#2
delete from t2 where c1 = 2;

delete from t1 where c1 = 2;




@capture_off



Comments