求助SQL锁定记录的问题

[oo渣渣oo]

数据库:SQL SERVER 2000

数据表: MAINSERIAL,只有一个字段PREVSERIAL,并且只有一条记录

存储过程:

ALTER PROCEDURE [DBO].[GETMAINSERIAL]
AS
	DECLARE @SERIAL BIGINT
	SELECT @SERIAL = PREVSERIAL FROM MAINSERIAL

	IF @SERIAL = 9999999999 BEGIN SET @SERIAL = 0

	SET @SERIAL = @SERIAL +1
	UPDATE MAINSERIAL SET PREVSERIAL = @SERIAL
	
	SELECT 'CS' + REPLACE( STR(@SERIAL,10,0), ' ' , '0' )
	RETURN

存储过程的作用:从数据表MAINSERIAL中读取上次使用的流水号,自动+1,再写回去.

问题:
单应用进程调用时不存在问题.
现在问题是:当两个以上的应用进程同时调用时,可能出现返回同样的流水号的情况,与流水号必须唯一的设计思想不符.

应该的解决方案:
我的想法是,这个存储过程应该这样处理:
1.锁记录,或锁表
2.读取流水号
3.自动+1
4.写回新流水号
5.解锁
6.返回数据

但是我不知道怎么写,请高人指点.注意在2-4的过程中,其他进程对此存储过程的调用应该都必须是等待状态!
最终目的是让所有的进程都取到唯一的流水号.

[飘零一叶]

楼主可将PREVSERIAL这个字段加上索引，就把数据库的隔离级别设置为可串行读

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

[-晴天]

create table MAINSERIAL(PREVSERIAL nvarchar(10))
insert into mainserial select '0000000001'
go
CREATE PROCEDURE [DBO].[GETMAINSERIAL]
AS
update mainserial set prevserial=
(case when prevserial='9999999999' then '0000000000' else
right('0000000000'+convert(nvarchar(10),prevserial+1),10) end)
go
exec getmainserial
select * from mainserial
go
drop table mainserial
drop procedure getmainserial
/*
PREVSERIAL
----------
0000000002

(1 行受影响)

*/

[飘零一叶]

楼主还可以尝试一下，给你这个存储过程加锁。

exec sp_getapplock 'ProcLock','Exclusive','Session','PrcoUserRole'
exec [DBO].[GETMAINSERIAL]
exec sp_releaseapplock 'ProcLock','Session'

[oo渣渣oo]

[Quote=引用 5 楼 wtnu200 的回复:]
没用？你有没有定义隔离级别，
Read Committed
[/Quote]

呃...不会...给写下?

[oo渣渣oo]

[Quote=引用 7 楼 abcdwell 的回复:]
http://www.sql-server-performance.com/articles/dba/concurrency_sp_locks_p1.aspx

帮顶，
[/Quote]

鸟语啊?看不懂.....

[xman_78tom]

if object_id('dbo.sequence') is not null
 drop table dbo.sequence;
go
create table dbo.sequence (id bigint);
go
insert into dbo.sequence values(0);
go

if object_id('dbo.get_sequence') is not null
 drop procedure dbo.get_sequence
go
create procedure dbo.get_sequence
as
 declare @id bigint;
 update sequence 
  set @id=(case when id<9999999999 then id else 0 end), id=@id+1;
 
 select 'cs'+right('000000000'+ltrim(@id),10);
go

-- 测试
-- 会话 1
while 1=1
exec dbo.get_sequence;

-- 会话 2
while 1=1
exec dbo.get_sequence;

[wuyq11]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
begin tran
select * from tablename with (rowlock) where id=1
waitfor delay '00:00:05'
commit tran
B连接中如果执行
update tablename set colname='10' where id=1 --则要等待5秒
update tablename set colname='10' where id<>1 --可立即执行

update mainserial set prevserial=
(case when prevserial<9999999999 then right('0000000000'+convert(nvarchar(10),prevserial+1),10) else '0000000000' end),@id='CS' + (case when prevserial<9999999999 then right('0000000000'+convert(nvarchar(10),prevserial+1),10) else '0000000000' end)
FROM mainserial WITH (TABLOCKX)

[wtnu200]

在存储过程中加事务,一个用户操作就锁定这个表.

[Ny-6000]

使用存储过程统一调用了.

[oo渣渣oo]

试过加TRANSACTION,无用....

[wtnu200]

没用？你有没有定义隔离级别，
Read Committed

[mooniscrazy]

为什么不用Sql2000自带的select @@identity ?

[abcdwell]

http://www.sql-server-performance.com/articles/dba/concurrency_sp_locks_p1.aspx

帮顶，

[-晴天]

加一个事务不就行了么.

[allanli]

这里面还涉及一个问题是你这个流水号是什么时候产生呢?
一般如果是单据流水号的话最好是在保存的时候再判断一次这个流水号有没有重号,有重号的话再产生一个.
因为这个流水号是点击添加的时候产生的,那么如果用户久久都没有保存或者干脆后来取消了添加
那么你这个流水号怎么处理呢?如果你在添加后产生了流水号马上就登记这个流水号已经使用的话就会面临上面问题.所以最好是在保存的时候才登记这个流水号使用,添加的时候只是产生流水号而不登记,在保存的时候再判断是否重号.

[allanli]

但这样就会遇到添加的时候是这个流水号,而保存后变成别的流水号的问题的(但这个没什么关系的,流水号最重要就是不会重号就行了,关键是保存后打印的那个)

还有一个方法是添加的时候马上登记这个流水号,并使用事务隔离,保证不会重号
并且在产生新号前判断号码的连续性,而不是简单的用最大值加一
不然就会产生跳号的情况.但这样又会带来一个问题是后添加的用户产生的流水号不一定是比先添加的大.

呵呵,说了一大通自己系统遇到的问题,希望对大家有启发,更希望大家说一下自己在单据流水号管理的经验

[yongjinghuang]

这是你程度逻辑设计有问题，同意楼上的意见。插入时生成流水号，大部分的流水号基本都这样生成。

[oo渣渣oo]

感谢各位,我已经用WITH TABLOCKX解决了.

回楼上两位:我是在插入的时候才生成,不过因为这个模块应用到很多个程序,所以有可能两个程序同时在取流水号,才造成现在的情况.锁表后就OK了.