SQL处理ACID和并发性

Question

(请尽可能笼统地回答。但是我在和MySql中工作，所以如果没有一般的答案，请继续回答其中的一个或两者。)

考虑在SQL数据库中实现的预订系统。我想确保在许多多个用户中，只有一个用户获得了预订，而没有其他用户“认为”他们得到了它。这是DB工作中典型的并发问题，但我不确定最佳答案是什么。

具体情况：

假设每个用户都有一个UserID。我们可以想象，一些用户正在尝试使用UserID值1004、1005、1009和1011进行预订。

假设资源和预订存储在一张桌子上，即座位。我们可以想象一下，座位桌上有一段时间：

    ----- SEATS -----------------------------
    SeatID   UserID   ResvTime
       1      1017    2014.07.15 04:17:18.000
       2      NULL    NULL
       3      NULL    NULL
       4      1012    2014.07.15 04:19:35.000
       5      1003    2014.07.15 04:20:46.000
    -----------------------------------------

现在假设“同时”，用户1004和1005试图获取SeatID 3，我想知道什么才能正确地确保他们中只有一个获得席位，而另一个被拒绝。在think中，我能想到的最简单的代码版本是：

  PROC GRABSEAT @seatid INT, @userid INT, @obtained BIT OUTPUT
  BEGIN
     DECLARE @avail INT
     SET @avail = (SELECT UserID FROM SEATS WHERE (SeatID = @seatid))
     IF (@avail IS NULL)
        BEGIN
           UPDATE SEATS SET UserID = @userid, ResvTime = GETDATE() WHERE (SeatID = @seatid)
           SET @obtained = 1
        END
     ELSE
        SET @obtained = 0
  END

但问题是，如何防止这种情况允许多个并发用户(所有执行此PROC的用户)在同一个位置上获得真正的回报(比如SeatID = 3)。

例如，如果用户1004和1005几乎同时执行这个PROC，那么在他们中的任何一个尝试设置UserID列之前，他们都可以执行SELECT和get @ either = NULL。然后他们都会运行UPDATE语句。假设没有更糟的结果，那么其中一个会覆盖另一个的集合，他们都会认为自己得到了座位，但实际上只有最后运行UPDATE语句的人才会将他们的数据存储在席位表中。另一个则会覆盖他们的数据。这被称为“输入丢失”问题。但是，在SQL数据库中防止它的方法是什么呢？我一直假设每个SQL语句都是作为事务执行的。事务具有四个所谓的"ACID“属性。这些属性是我所需要的。因此，我认为，在SQL数据库中，答案是：

  BEGIN TRANSACTION
  EXCEUTE GRABSEAT @seatid= <id1>, @userid = <id2>, @obtained
  COMMIT

通过这样做，我需要的主要属性(隔离)将保证我所担心的交错执行不会发生。

，但是我看过一些文章说它一点也不简单，，我认为很多文章指出的一个大问题是，并不是每个事务都运行在完全的原子性和隔离状态下。因此，在事务中进行上述包装可能无法达到预期的结果。如果没有，那需要什么呢？

Answer 1

从定义上来说，事务是原子的。但是，当事务的更改对其他用户可见时/连接/事务依赖于隔离水平。Server中的默认隔离已被提交--有关如何更改它的更多信息和链接，请参见这个问题的答案。

对于这种场景，您可能需要SERIALIZABLE。好消息是，您可以在存储的proc中使用SET TRANSACTION ISOLATION LEVEL语句更改事务的隔离级别。坏消息是，您必须100%确信这是代码中唯一更新SEAT表的位置。

从根本上讲，你面临的问题是存在种族问题。仅仅因为您在一个事务中，并不意味着两个事务不能同时调用存储的proc，然后运行SELECT。现在，两个tx都认为进行更新是可以的。将隔离级别设置为SERIALIZABLE将锁定访问SELECT的tx的表。

Answer 2

与SELECT语句不同，为什么不直接进行更新，在NULL上添加一个额外的筛选器，这样如果值为null，它就不能替换，然后返回查询是否有效。这样，事务是原子的，因为它只是一个查询。

PROC GRABSEAT @seatid INT, @userid INT, @obtained BIT OUTPUT
BEGIN
    UPDATE SEATS SET UserID = @userid, ResvTime = GETDATE() 
    WHERE (SeatID = @seatid) AND UserID IS NULL
    SET @obtained = @@ROWCOUNT
END

由于行锁定，两个更新不能同时进行，因此一个将工作(返回@@ROWCOUNT = 1，另一个将失败@@ROWCOUNT = 0。