避免在SQL Server 2008中使用MAX + 1整数进行并发问题...制作自己的IDENTITY值 [英] Avoiding concurrency problems with MAX+1 integer in SQL Server 2008... making own IDENTITY value

问题描述

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

I need to increment an integer in a SQL Server 2008 column.

听起来应该使用IDENTITY列，但我需要为每个客户增加单独的计数器.考虑一个电子商务站点，其中每个客户都有自己的递增订单号，从1开始.值必须是唯一的(每个客户).

Sounds like I should use an IDENTITY column, but I need to increment separate counters for each of my customers. Think of an e-commerce site where each customer gets their own incrementing order number, starting with 1. The values must be unique (per customer).

例如，

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

从本质上讲，由于客户数量没有限制，并且每个客户都需要order #计数器，因此我将需要手动执行SQL的identity函数.

Essentially, I will need to manually do the work of SQL's identity function since the number of customers is unlimited and I need order # counters for each of them.

我很舒服:

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表.

My problem is locking and concurrency concerns and assuring a unique value. It seems we need to lock with TABLOCKX. But this is a high volume database and I can't just lock the whole Orders table every time I need to do a SELECT MAX+1 process and insert a new order record.

但是，如果我不锁定整个表，那么我可能不会为该客户获得唯一的价值.由于我们的某些订单输入是事后通过多线程Windows进程分批完成的，因此可能有两个操作同时要为同一位客户插入新订单.

But, if I don't lock the whole table, then I might not get a unique value for that customer. Because some of our order entry is done after-the-fact in batches by a multi-threaded Windows process, it is possible that 2 operations will be simultaneously wanting to insert a new order for the same customer.

那么什么样的锁定方法或技术可以避免死锁，又让我为每个客户保持唯一的递增订单号?

So what locking methodology or technique will avoid deadlocks and still let me maintain unique incrementing order numbers PER customer?

推荐答案

我将介绍一个表格，以保留每个客户的最后一个号码 使用订单生成在同一事务中查询和更新它.

I would introduce a table to keep last number per customer to query and update it in the same transaction with order generation.

TABLE CustomerNextOrderNumber
{
    CustomerID id PRIMARY KEY,
    NextOrderNumber int
}

在同一客户同时下两个订单时，select上的更新锁定将有助于避免竞争情况.

Update lock on select will help to avoid race condition when two orders are placed concurrently by the same customer.

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启发) 这里的更新锁是由第一次更新强加的.

Similar, but more straightforward approach (inspired by Joachim Isaksson) update lock here is imposed by the first update.

BEGIN TRANSACTION

DECLARE @NextOrderNumber INT

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

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

...

COMMIT

这篇关于避免在SQL Server 2008中使用MAX + 1整数进行并发问题...制作自己的IDENTITY值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！