行级锁存储过程,获取序列号。
create or replace procedure Get_Next_Seq(
inSchema in nvarchar2,
inTable in nvarchar2,
outIndex out number ) is
begin
select t_seq.next_value + t_seq.step_value into outIndex
from ENTITY_SEQ t_seq where
t_seq.schema_name = inSchema and t_seq.table_name = inTable
and rownum =1
for update wait 3;
update ENTITY_SEQ t_seq set t_seq.next_value = outIndex where
t_seq.schema_name = inSchema and t_seq.table_name = inTable;
commit;
exception
when others then
rollback;
end Get_Next_Seq;
测试脚本,一份脚本,开多个窗口同时跑。
declare
testTimes number(10) := 0;
outindex number(10) := 0;
begin
-- Call the procedure
while testTimes < 20000 loop
get_next_seq('PROJ_USER',
'ARTICLE',
outindex);
testTimes := testTimes + 1;
begin
insert into TEMP_RESULT(ENTITY_INDEX)
values(outindex);
commit;
end;
end loop;
end;
检查结果。
select * from TEMP_RESULT t_seq
group by t_seq.entity_index
having count(t_seq.entity_index) > 1;
将存储过程中的下面的子句去掉,取消行级锁重新测试,出现重复值。
for update wait 3;