logo标签列表

避免SQL Server 2008中的MAX+1整数并发问题... 自己生成IDENTITY值

sqllockingidentitymaxincrement
22

我需要在 SQL Server 2008 列中增加一个整数值。

听起来我应该使用 IDENTITY 列，但我需要为每个客户单独增加计数器。想象一下电子商务网站，每个客户都有自己的递增订单号，从1开始。这些值必须是唯一的（每个客户都唯一）。

例如：

Customer1  (Order #s 1,2,3,4,5...)
Customer2  (Order #s 1,2,3,4,5...)

基本上，由于客户数量是无限的，并且我需要为每个客户提供“订单号”计数器，所以我需要手动完成SQL的identity函数的工作。

我相当熟练于进行以下操作：

BEGIN TRANSACTION
  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

我的问题涉及锁定和并发问题以及确保唯一值。似乎我们需要使用TABLOCKX进行锁定。但这是一个高流量的数据库，每次需要执行SELECT MAX+1过程并插入新订单记录时，我不能仅锁定整个Orders表。

但是，如果我不锁定整个表，那么可能无法为该客户获得唯一值。由于我们的部分订单录入是通过多线程Windows进程在事后批处理完成的，因此可能会有两个操作同时想要为同一客户插入新订单。

那么，哪种锁定方法或技术可以避免死锁，并仍然让我维护每个客户的唯一递增订单号？

可能是重复的问题：SQL Server：嵌套事务是否足以从数据库获取唯一数字？ - GSerg
7个回答
10

在SQL Server 2005及之后的版本中，最好以原子方式完成此操作，而不使用任何事务或锁定：

update ORDERS 
set OrderNumber=OrderNumber+1 
output inserted.OrderNumber where CustomerID=@ID
9
我会引入一张表格来记录每个客户的最后一个数字，以便在生成订单时在同一事务中进行查询和更新。
TABLE CustomerNextOrderNumber
{
    CustomerID id PRIMARY KEY,
    NextOrderNumber int
}

当同一客户同时下两个订单时，对选择进行更新锁定有助于避免竞争条件。
BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

SELECT @NextOrderNumber = NextOrderNumber
FROM  CustomerNextOrderNumber (UPDLOCK)
WHERE CustomerID = @CustomerID

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID


... use number here


COMMIT

类似的，但更为直接的方法（灵感来自Joachim Isaksson）在此处使用第一次更新时强制执行更新锁。
BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID

SELECT @NextOrderNumber = NextOrderNumber
FROM CustomerNextOrderNUmber
where CustomerID = @CustomerID

...

COMMIT
4
使用 "update CustomerNextOrderNumber set NextOrderNumber=NextOrderNumber+1 output inserted.NextOrderNumber where Customerid=?"：该语句用于更新名为"CustomerNextOrderNumber"的表格中特定客户的下一个订单编号，使其加一，并返回被更新的"NextOrderNumber"值。具体的客户由"Customerid=?"指定。 - Joachim Isaksson
@Joachim Isaksson：经过思考，我更喜欢你的方法 :) - alexm
其实我的建议只有一个句子，不是两个。 - Joachim Isaksson
Alexm，我们在使用UPDLOCK进行压力测试时遇到了很多死锁。虽然这似乎是一个不错的方法，但对我们来说非常奇怪的是，当我们切换到TABLOCKX时，压力测试没有产生任何死锁（这毫无意义），也没有产生重复的ID。你知道为什么吗？我的阅读材料表明它会对整个表进行硬锁定，并且应该会产生死锁。我同意你的方法，即我们应该尝试将锁定限制在适用的客户号上，但在测试期间结果是不合逻辑的。 - stackonfire
@Joachim Isaksson：确实，我并没有字面上理解你的回答 :) 我会仔细研究新的 SQL 2008 "OUTPUT" 关键字。 - alexm
@stackonfire：死锁通常发生在获取锁的顺序不一致时。为了解决这个问题，可以将关键代码放在单独的存储过程中。另一个导致死锁的原因是锁升级——我有时使用应用程序锁来避免这种情况。 - alexm
4

默认的事务级别是“读取提交”（read committed），它不能保护您免受幻读的影响。幻读是指在您的 select 和 insert 之间，另一个进程插入了一行：

BEGIN TRANSACTION
SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

即使是更高的隔离级别，如可重复读取，也不能保护您免受幻读的影响。只有最高的隔离级别——串行化，才能防止幻读。

因此，解决方案之一就是使用最高的隔离级别：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
...

另一种解决方法是使用 tablockx、holdlock 和 updlock 表提示，以确保只有您的事务可以修改表。第一个锁定表格，第二个保持锁定直到事务结束，第三个抓取一个 select 的更新锁，因此不必稍后升级。
SELECT @NewOrderNumber = MAX(OrderNumber)+1 
From Orders with (tablockx, holdlock, updlock)
where CustomerID=@ID

如果在CustomerID上建立索引，这些查询将非常快速，所以不用太担心并发问题，特别是如果您每分钟的订单量少于10个。
Andomar，感谢您的回复。在压力测试期间，我们在测试SERIALIZABLE时遇到了太多死锁情况。这就是为什么我们尝试使用TABLOCKX的原因。我们没有使用HOLDLOCK进行测试。我们确实会在CustomerID上建立索引，但知道在批处理中输入订单批次时，每秒将有>10个订单，并且其中一些订单将具有相同的CustomerID，并且使用多个线程。 - stackonfire
好的，tablockx 没有 holdlock 会导致死锁：在 select 和 update 之间表锁将被释放。因此，请尝试使用 holdlock 进行测试。您还可以尝试使用 updlock 查询提示进行测试，尽管 tablockx 应该已经涵盖了这个领域。 - Andomar
4
你可以这样做：
BEGIN TRANSACTION
  SELECT ID
  FROM Customer WITH(ROWLOCK)
  WHERE Customer.ID = @ID

  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

我们现在只锁定了客户表中的一个客户而不是所有客户，每当两个人同时尝试为同一客户添加订单时，谁先锁定了客户就获胜，另一个人就必须等待。
如果人们为不同的客户插入订单，它们不会互相干扰！
以下是其工作方式：
- User1开始为ID为1000的客户插入订单。 - User2尝试为ID为1000的客户插入订单。 - User2必须等待User1完成插入订单。 - User1插入订单并提交事务。 - User2现在可以插入订单，并保证获得客户1000的真实最大orderId。
谢谢。您能解释一下为什么需要第一个查询(select ID from Customer with (rowlock) where Customer.ID=@ID)吗？锁定关注的是订单表，即使该SELECT语句限制只针对一个客户ID。 - stackonfire
@stackonfire，我添加了一些更多的解释，请让我知道是否还不清楚。 - Bassam Mehanni
0

是否可以为每个客户创建一个带有IDENTITY字段的表，然后您可以将新记录插入到客户表中并从中提取值。

Antony，我无法为每个客户创建单独的IDENTITY字段，因为客户数量很大且不断增加。 - stackonfire
0

你试图关联两个完全不同的需求。

即使你让它工作了，如果客户A删除了一个早期订单，你会重新编号他们所有现有的记录以保持它们连续并从1开始吗？那将是一个锁定问题....

给记录一个标识（或可能是GUID）。当你需要计数时，查询它，如果你想要行号（我自己从来没有看到过这个点），使用rowno。

你不需要为每个客户自动增加顺序，你不想要一个，而且没有大量的锁定就不能有一个。

横向思考时间。

如果你提供

Order Description Date Due
1     Staples     26/1/2012
2     Stapler     1/3/2012
3     Paper Clips 19/1/2012

这并不意味着（实际上也不应该意味着）订单键是1、2和3，只要它们满足唯一性要求，它们可以是任何东西。

Tony，感谢您的回复。我们不会删除早期的订单号。我同意这种情况并不理想，与使用标识或其他SQL ID相比，但业务逻辑要求采用这种方法。当然，实际细节比我发布的更加复杂。原因实际上是很好的。客户必须按编号引用其唯一的订单ID，所以我可以仅计算它们。无论我是否能够辩论业务逻辑，这个问题在理论上仍然非常有趣。 - stackonfire
1
不需要，这是关注点分离的时间。你正在让你的数据库层变得非常复杂、脆弱和昂贵，只是为了让客户通过键引用订单。替代键是可行的方法，如果扩展性是个问题，那就使用 GUID，这正是它们存在的目的。这个问题唯一有趣的地方就是人们陷入这个陷阱的频率有多高。 - Tony Hopkinson
0
create table TestIds
(customerId int,
nextId int)

insert into TestIds
values(1,1)
insert into TestIds
values(2,1)
insert into TestIds
values(3,1)

go

create proc getNextId(@CustomerId int)
as

declare @NextId int

while (@@ROWCOUNT = 0)
begin
    select @NextId = nextId
    from TestIds
    where customerId = @CustomerId

    update TestIds
    set nextId = nextId + 1
    where customerId = @CustomerId
    and nextId = @NextId

end

select @NextId  
go
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接