sql - T-SQL并发问题: Auction/Bidding System

Question

我目前正在使用 ASP.NET 3.5 和 SQLServer 2008 开发在线拍卖系统。我已经达到了开发阶段，我需要确保我的系统明智地处理在以下情况下可能出现的并发问题:

杰拉尔丁 (Geraldine) 和约翰 (John) 两个人想要竞拍同一件拍卖品，目前售价为 50 英镑。杰拉尔丁 (Geraldine) 的出价为 55 英镑，约翰 (John) 的出价为 52 英镑。系统现在有两个页面“submit_bid.aspx”正在运行；该页面的每个副本都会检查其出价是否足够高，他们都看到了这一点，然后提交了出价。如果约翰的出价首先通过，那么拍卖元素的价格当前为 55 英镑，稍后就会被 52 英镑的出价取代。

我需要做的是锁定拍卖项目行，直到更新当前出价，然后再允许任何其他出价者检查当前出价并提出新出价。

我的问题是:使用 T-SQL 和/或 ADO.NET 执行此操作的最佳实践方法是什么？

我目前有一个 AuctionItem 表，其中包含以下字段(以及为简洁起见而未包含的其他字段):

AuctionItemID   INT
CurrentBidPrice MONEY
CurrentBidderID INT

我进行了一些研究并提出了以下 T-SQL(伪代码):

@Bid MONEY
@AuctionItemID INT

BEGIN TRANSACTION

SELECT @CurrentBidPrice = CurrentBidPrice
FROM AuctionItem
WITH (HOLDLOCK, ROWLOCK)
WHERE AuctionItemID = @AuctionItemID

/* Do checking for end of Auction, etc. */

if (@Bid > @CurrentBidPrice)
BEGIN
  UPDATE AuctionItem
  SET CurrentBidPrice = @Bid
  WHERE AuctionItemID = @AuctionItemID
END

COMMIT TRANSACTION

我还了解到，如果包含SET LOCK_TIMEOUT，我还可以减少并发更新失败的数量。例如:

SET LOCK_TIMEOUT 1000

...将使并发更新等待 1000 毫秒以释放锁。这是最佳实践吗？

Answer 1

来源:“chrisrlong”，http://www.dbasupport.com/forums/archive/index.php/t-7282.html

以下是用于处理多用户并发问题的方法:

1. 什么都不做(不需要)

   • 用户 1 读取一条记录
   • 用户 2 读取同一条记录
   • 用户 1 更新了该记录
   • 用户 2 更新了同一条记录

   用户 2 现在已覆盖用户 1 所做的更改。它们完全消失了，就好像它们从未发生过一样。这称为“丢失更新”。

2. 悲观锁定(在读取记录时锁定记录。)

   • 用户 1 读取记录并通过在记录上设置排他锁(FOR UPDATE 子句)来锁定它
   • 用户 2 尝试读取并锁定同一记录，但现在必须在用户 1 后面等待
   • 用户 1 更新记录(当然还有提交)
   • 用户 2 现在可以读取包含用户 1 所做更改的记录
   • 用户 2 使用用户 1 的更改更新记录

   丢失更新的问题已解决。这种方法的问题是并发性。用户 1 正在锁定他们可能永远不会更新的记录。用户 2 甚至无法读取该记录，因为他们在读取时也需要独占锁。这种方法需要太多的独占锁定，并且锁的生命周期太长(通常跨越用户控制 - 绝对不能)。这种方法几乎从未被实现。

3. 使用乐观锁定。
   乐观锁在读取时不使用排它锁。相反，在更新期间会进行检查，以确保记录自读取以来没有被更改。通常，这是通过添加 version/etc 列(INT/numeric，保存在执行 UPDATE 语句时增加的数值)来完成的。 IE:

   UPDATE YOUR_TABLE
      SET bid = 52
    WHERE id = 10
      AND version = 6
   

   另一种选择是使用时间戳，而不是数字列。除了实现乐观并发之外，此列没有其他目的。它可以是数字或日期。这个想法是在插入行时为其赋予一个值。每当读取记录时，时间戳列也会被读取。执行更新时，将检查时间戳列。如果更新时的值与读取时的值相同，则一切正常，执行更新并且时间戳发生更改!。如果更新时的时间戳值不同，则会向用户返回错误 - 他们必须重新读取记录，重新进行更改，并尝试再次更新记录。

   • 用户 1 读取记录，包括时间戳 21
   • 用户 2 读取记录，包括时间戳 21
   • 用户 1 尝试更新记录。 had中的时间戳(21)与数据库中的时间戳(21)匹配，因此执行更新，时间戳为update(22)。
   • 用户 2 尝试更新记录。手中的时间戳 (21) 与数据库中的时间戳 (22) 不匹配，因此返回错误。用户 2 现在必须重新读取记录，包括新的时间戳 (22) 和用户 1 的更改，重新应用其更改并重新尝试更新。

比较

• 乐观锁定独立于数据库 - 无需修改隔离级别和隔离级别的数据库特定语法。
• 我会在时间戳上使用数字列 - 数据更少且管理麻烦