求教单号争用问题（重复键）

[shadowdudu]

DECLARE @i VARCHAR(14)--取最大单号避免重复
SELECT @i=MAX(cast(EEA001 as decimal(18,0)))+cast( floor(rand()*100) as int) FROM dbo.RMSEEA
--主表
insert into RMSEEA (EEA001,EEA004,EEA002,EEA003,EEA005,EEA006,EEA007,EEA008,EEA009,EEA010,EEA011,EEA012,EEA013,EEA014,EEA015,EEA016,EEA017,EEA018,EEA019,EEA020,EEA027,EEA028,EEA029,EEA030,EEA031,EEA032,EEA033, EEA034,EEA035,EEA036,EEA902,EEA037,EEA038,EEA039) values (@i,'51','20110609','1011','002','101','767','990003','','','','',1.0000,5000.0000,5000.0000,0.0000,0.0000,0.0000,0.0000,5000.0000,@i,0.0000,0.0000,0.0000,0.0000,'','','',0.0000,0.0000,'2011-06-09 09:30:29:510',0.0000,0,0)
--主表

--明细表
DECLARE @k INT --循环次数
DECLARE @j VARCHAR(10)--明细单号
SET @k=0
WHILE @k<100
BEGIN
SET @k=@k+1
SET @j=RIGHT('000' + CAST(@k AS varchar(10)),3) --明细单号补零
select EEB001,EEB002,EEB003,EEB004,EEB005,EEB006,EEB007,EEB008,EEB009,EEB010,EEB011,EEB012,EEB013,EEB020,EEB021,EEB022,EEB023 from RMSEEB

insert into RMSEEB (EEB001,EEB002,EEB003,EEB004,EEB005,EEB006,EEB007,EEB008,EEB009,EEB010,EEB011,EEB012,EEB013,EEB020,EEB021,EEB022,EEB023) values (@i,@j,'00002','','','件',1.0000,'********************',5000.0000,1.0000,5000.0000,'F','F','',0.0000,'','51')

END[/code]

[shadowdudu]

[Quote=引用 4 楼 fredrickhu 的回复:]
设置好事务的隔离级别 你这个明显是并发上出了问题
多人读写导致的重复写入
[/Quote]

这个隔离级别咋弄？ 我现在这样判断

DECLARE @i VARCHAR(14)--取最大单号避免重复
SELECT @i=MAX(cast(EEA001 as decimal(18,0)))+cast( floor(rand()*100) as int) FROM RMSEEA
select EEA001,EEA004,EEA002,EEA003,EEA005,EEA006,EEA007,EEA008,EEA009,EEA010,EEA011,EEA012,EEA013,EEA014,EEA015,EEA016,EEA017,EEA018,EEA019,EEA020,EEA027,EEA028,EEA029,EEA030,EEA031,EEA032,EEA033,EEA034,EEA035,EEA036,EEA902,EEA037,EEA038,EEA039 from RMSEEA


IF EXISTS(SELECT EEA001 FROM RMSEEA WHERE EEA001=@i)--防止重复
BEGIN
   SELECT @i=MAX(cast(EEA001 as decimal(18,0)))+cast( floor(rand()*100) as int) FROM RMSEEA
END

ELSE
BEGIN
insert into RMSEEA (EEA001,EEA004,EEA002,EEA003,EEA005,EEA006,EEA007,EEA008,EEA009,EEA010,EEA011,EEA012,EEA013,EEA014,EEA015,EEA016,EEA017,EEA018,EEA019,EEA020,EEA027,EEA028,EEA029,EEA030,EEA031,EEA032,EEA033, EEA034,EEA035,EEA036,EEA902,EEA037,EEA038,EEA039) values  (@i,'51','20110609','1011','002','101','767','990003','','','','',1.0000,5000.0000,5000.0000,0.0000,0.0000,0.0000,0.0000,5000.0000,@i,0.0000,0.0000,0.0000,0.0000,'','','',0.0000,0.0000,'2011-06-09 09:30:29:510',0.0000,0,0)

[shadowdudu]

等高手解决

[shadowdudu]

补充下 单独运行的时候没有问题，可是当多人插入的时候就出现了插入重复键的问题

[-晴天]

用不着这样麻烦的.
设置一个自增列,你的那个单号在自值列基础上建一个计算列就行了.
如果不这样做,那很简单,从这个过程读表开始,就将表锁住,排他锁,不让别人读写,直到插入完成.

[--小F--]

[Quote=引用 2 楼 shadowdudu 的回复:]
补充下 单独运行的时候没有问题，可是当多人插入的时候就出现了插入重复键的问题
[/Quote]
设置好事务的隔离级别 你这个明显是并发上出了问题
多人读写导致的重复写入