sql-server - 我如何为这个 SQL 获得正确的锁？

Question

我的数据库是 SQL Server 2005/8。在预订系统中，我们对一个事件有 24 个预订的限制。存储过程中的这段代码检查:- 当前用户 (@UserId) 尚未预订事件 (@EventsID)- 当前事件有 24 岁以下的当前预订名单- 插入新预订。

BEGIN TRANSACTION 
IF (((select count (*) from dbo.aspnet_UsersEvents with (updlock) 
      where UserId = @UserId and EventsId = @EventsId) = 0) 
AND  ((SELECT Count(*)  FROM dbo.aspnet_UsersEvents with (updlock) 
      WHERE EventsId = @EventsId) < 24))
BEGIN
  insert into dbo.aspnet_UsersEvents (UserId, EventsId) 
      Values (@UserId, @EventsId)
END
COMMIT

问题是它不安全。两个用户可能同时执行测试并得出他们都可以预订的结论。两者都插入一行，我们最终得到 25 个预订。

简单地将其包含在事务中是行不通的。我尝试将 WITH (UPDLOCK) 添加到选择中，希望其中一个会获取更新锁并将另一个拒之门外。那是行不通的。

Answer 1

三个选项:

1. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
2. 将锁定提示更改为 WITH (UPDLOCK, HOLDLOCK)
3. 将唯一约束添加到 dbo.aspnet_UsersEvents 并围绕插入添加 TRY/CATCH。

如果省略 HOLDLOCK，您可以使用以下脚本来确认锁已被获取并立即释放。当使用 HOLDLOCK 时，您还会看到锁未释放(没有“releasing lock reference on KEY”输出)。

( Gist script )