在SQL Server中生成重复的自动编号
问题描述:
要温柔,我是一个SQL新手。我有一个表命名autonumber_settings这样的:在SQL Server中生成重复的自动编号
Prefix | AutoNumber
SO | 112320
CA | 3542
一个只要创建一个新的销售线,存储过程被称为读取从“SO”行目前的自动编号值,然后递增的数量,更新,相同的行,并从存储过程返回数字。该存储过程是如下:
ALTER PROCEDURE [dbo].[GetAutoNumber]
(
@type nvarchar(50) ,
@out nvarchar(50) = '' OUTPUT
)
as
set nocount on
declare @currentvalue nvarchar(50)
declare @prefix nvarchar(10)
if exists (select * from autonumber_settings where lower(autonumber_type) = lower(@type))
begin
select @prefix = isnull(autonumber_prefix,''),@currentvalue=autonumber_currentvalue
from autonumber_settings
where lower(autonumber_type) = lower(@type)
set @currentvalue = @currentvalue + 1
update dbo.autonumber_settings set autonumber_currentvalue = @currentvalue where lower(autonumber_type) = lower(@type)
set @out = cast(@prefix as nvarchar(10)) + cast(@currentvalue as nvarchar(50))
select @out as value
end
else
select '' as value
现在,有访问一个复制命令,复制两者报头和线相同的表的另一过程。有时,重复会导致重复的行号。这里是一块程序:
BEGIN TRAN
IF exists
(
SELECT *
FROM autonumber_settings
WHERE autonumber_type = 'SalesOrderDetail'
)
BEGIN
SELECT
@prefix = ISNULL(autonumber_prefix,'')
,@current_value=CAST (autonumber_currentvalue AS INTEGER)
FROM autonumber_settings
WHERE autonumber_type = 'SalesOrderDetail'
SET @new_auto_number = @current_value + @number_of_lines
UPDATE dbo.autonumber_settings
SET autonumber_currentvalue = @new_auto_number
WHERE autonumber_type = 'SalesOrderDetail'
END
COMMIT TRAN
为什么这两个程序似乎并没有发挥好在一起,偶尔给从头创建为复制创建行相同的行号任何想法。
答
这是一个竞赛条件或您的自动编号分配。在将新数据写回数据库之前,两次执行可能会读出相同的值。
解决此问题的最佳方法是使用标识列并让SQL服务器处理自动编号分配。
除非您可以使用sp_getapplock来序列化您对autonumber_settings的访问权限。
答
您可以对选择使用可重复读取。这将锁定行并阻止其他过程的选择,直到您更新值并提交。
在每个select的from子句之后插入WITH(REPEATABLEREAD,ROWLOCK)。
+0
这是一个有趣的想法,让我读一下这一点。 – npeterson 2012-02-23 23:29:26
我强烈建议使用IDENTITY列,这正是他们设计要避免的。 – 2012-02-23 23:18:10
整个'SELECT ... /增加一个/ UPDATE'循环不是并发安全的 - 多个进程可以获取相同的起始值,将其加1,然后写回新值。你需要(a)使用'INT IDENTITY'来代替(那些保证是并发安全的),(b)等待SQL Server 2012获得'SEQUENCE'对象,或者(c)变成单个'UPDATE'语句不能被多个调用者多次执行 – 2012-02-24 06:03:04
另外:**为什么**您将'@ currentvalue'定义为'nvarchar(50)'变量?这不是一个数值吗?如果它是一个数字 - 将其声明为一个数字! – 2012-02-24 06:04:08