select * for update wait 3 行级锁使用以及测试方法；

行级锁存储过程，获取序列号。

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;