dbms_lock同步併發

/**
買珠還櫝,雖然CBO還沒搞明白，也能學點其它技巧。
在看Jonathan Lewis的CBO基礎時，看到一個控制併發的技巧，摘錄下來，以後做開發時做個參考. 讓5個會話的插入操作，併發執行. 原始碼如下：
*/[@more@]

-- 使用dbms_lock同步併發啟動時間

/**
買珠還櫝,雖然CBO還沒搞明白，也能學點其它技巧。
在看Jonathan Lewis的CBO基礎時，看到一個控制併發的技巧，摘錄下來，以後做開發時做個參考
讓5個會話的插入操作，併發執行
原始碼如下：
*/

create table t1(
date_ord date constraint t1_dto_nn not null,
seq_ord number(6) constraint t1_sqo_nn not null,
small_vc varchar2(10)
)
pctfree 90
pctused 10
;

drop sequence t1_seq;
create sequence t1_seq;

create or replace procedure t1_load(i_tag varchar2) as

m_date date;

begin
-- 同步併發啟動時間
dbms_output.put_line(
dbms_lock.request(
1,dbms_lock.s_mode, release_on_commit => true
)
);
commit;
for i in 0..25 loop
m_date := trunc(sysdate) + i;

for j in 1..200 loop
insert into t1 values(
m_date,
t1_seq.nextval,
i_tag || j
);
commit;
dbms_lock.sleep(0.01);
end loop;
end loop;
end;
/

begin
dbms_output.put_line(
dbms_lock.request(
1,dbms_lock.x_mode,
release_on_commit=>true
)
);
end;
/

prompt
prompt From five different sessions, execute a call to procedure
prompt t1_load with a different tag for each session, e.g.
prompt execute t1_LOAD('a')
prompt
prompt These will all suspend, waiting to acquire a share lock that
prompt this progam is holding in exclusive mode. When all five sessions
prompt have called the procedure, press return in this screen to allow them
prompt to continue

accept x

commit;