在项目中用到一个生成订单流水号的存储过程，有可能在项目不同的地方调用这个存储过程，本来希望每次调用存储过程都获取到不重复的单号，因该存储过程已用事务控制，程序开始时用begin tran，最后结束时才commit tran，中间如果发生错误就rollback tran并且return。但却发现这个事务好象没起作用，有时候两个不同的地方调用这个存储过程会取到相同的单号。后来分析了一下，可能是update sys_id set [email protected] where [email protected]，后面回滚事务抛出异常的语句没起作用，这样就造成调用该存储过程得到了最新的单号，而存储当前流水号的sys_id这个表的单号却没有同步更新。导致下一次调用该存储过程又会取到刚才已经取到的单号。下面将存储过程的代码贴出来，请高手帮忙看一下这个存储过程为什么事务控制没起作用。谢谢！
CREATE PROC [dbo].[proc_getsysid]
@bill_name varchar(20) ,
@sysid varchar(32) output
as
declare @newid varchar(32)
declare @idhead varchar(32)
declare @datetype varchar(32)
declare @billtype varchar(32)
declare @idnum int
declare @idcount int
declare @numcount int
begin tran
select @billtype=bill_type,@newid=new_id,@idhead=id_head,@datetype=date_type,@numcount=num_count from sys_id where [email protected]_name
if(@billtype is null)
begin
rollback tran
raiserror 50105 'WRONG BILL TYPE!'
return -1
end
set @sysid=''
if(upper(@idhead)!='NONE')
begin
set @sysid=upper(@idhead)
end
if(upper(@datetype)='YYYYMMDD')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),1,8)
end
else if(upper(@datetype)='YYMMDD')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),3,6)
end
else if(upper(@datetype)='YYYYMM')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),1,6)
end
else if(upper(@datetype)='YYMM')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),3,4)
end
else if(upper(@datetype)='YYYY')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),1,4)
end
else if(upper(@datetype)='YY')
begin
set @[email protected]+substring(convert(varchar,getdate(),112),3,2)
end
if(@newid is null)
begin
set @idcount=1
while(@idcount<@numcount)
begin
set @[email protected]+'0'
set @[email protected]+1
end
set @[email protected]+'1'
end
else if(@sysid=substring(upper(@newid),1,len(@sysid))and len(@sysid)>0 )
begin
set @idnum=convert(int,substring(@newid,len(@sysid)+1,@numcount))+1
if(len(@idnum)>@numcount)
begin
raiserror 50105 'OUT OF NUMBER!'
return -1
end
set @idcount=len(@idnum)
while(@[email protected]>0)
begin
set @[email protected]+'0'
set @[email protected]+1
end
set @[email protected]+convert(varchar,@idnum)
end
else if(len(@sysid)=0 and len(@newid)[email protected] )
begin
set @idnum=convert(int,substring(@newid,len(@sysid)+1,@numcount))+1
if(len(@idnum)>@numcount)
begin
raiserror 50105 'OUT OF NUMBER!'
return -1
end
set @idcount=len(@idnum)
while(@[email protected]>0)
begin
set @[email protected]+'0'
set @[email protected]+1
end
set @[email protected]+convert(varchar,@idnum)
end
else
begin
set @idcount=1
while(@idcount<@numcount)
begin
set @[email protected]+'0'
set @[email protected]+1
end
set @[email protected]+'1'
end
if @@error <> 0
begin
rollback tran
raiserror 50105 'GENERATE ERROR!'
return
end
update sys_id set [email protected] where [email protected]
if @@error <> 0
begin
rollback tran
raiserror 50105 'GENERATE ERROR!'
return
end
commit tran
GO
------解决方案--------------------
加了事务，你语句读完就释放了，别的继续读当然读到与刚才一样的，加锁一直接保持（HOLDLOCK）