下面二个过程占用锁资源多,还是 基本上没有区别,现在讨论的是这二个过程,请不要说用的别的功能实现
CREATE OR REPLACE PROCEDURE TL_GetNewSheetid1
(
v_shopid in varchar2,
v_serialnumberid in varchar2,
v_sheetid out varchar2
) as
begin
update serialnumber set seq =seq +1 where serialnumberid=v_serialnumberid ;
select v_shopid||to_char(seq) into v_sheetid from serialnumber where serialnumberid=v_serialnumberid ;
end ;
CREATE OR REPLACE PROCEDURE TL_GetNewSheetid2
(
v_shopid in varchar2,
v_serialnumberid in varchar2,
v_sheetid out varchar2
) as
row_serialnumber serialnumber%rowtype ;
begin
select * into row_serialnumber from serialnumber where serialnumberid=v_serialnumberid for update ;
update serialnumber set seq =seq +1 where serialnumberid=v_serialnumberid ;
select v_shopid||to_char(seq) into v_sheetid from serialnumber where serialnumberid=v_serialnumberid ;
end ;
CREATE OR REPLACE PROCEDURE TL_GetNewSheetid1
(
v_shopid in varchar2,
v_serialnumberid in varchar2,
v_sheetid out varchar2
) as
begin
update serialnumber set seq =seq +1 where serialnumberid=v_serialnumberid ;
select v_shopid||to_char(seq) into v_sheetid from serialnumber where serialnumberid=v_serialnumberid ;
end ;
CREATE OR REPLACE PROCEDURE TL_GetNewSheetid2
(
v_shopid in varchar2,
v_serialnumberid in varchar2,
v_sheetid out varchar2
) as
row_serialnumber serialnumber%rowtype ;
begin
select * into row_serialnumber from serialnumber where serialnumberid=v_serialnumberid for update ;
update serialnumber set seq =seq +1 where serialnumberid=v_serialnumberid ;
select v_shopid||to_char(seq) into v_sheetid from serialnumber where serialnumberid=v_serialnumberid ;
end ;
一个调用该过程的程序不结束(update不提交),另一个调用该过程的update就会一直等
这是什么用意?
for update后面注意及时commit,否则只读锁会一直存在