sql - 仅当总计数小于阈值时才向 SQL Server 表中插入一行

Question

我正在使用 SQL Server 2012。

我有一个存储用户的表。允许的最大用户数为 100 万。我有一个存储过程，我在其中注册用户。当我插入时，我想确保用户表中的总行数不会超过 100 万。我更愿意使用允许最大并发性的方法。

我相信我可以使用隔离级别为 SERIALIZABLE 的事务，然后首先计算行数，如果总计数小于 100 万，则插入。我的理解是 SERIALIZABLE 是非常有限制的，并且会随着并发增加而导致性能下降。

IF(SELECT COUNT(*) FROM Users) < 100000
BEGIN
    INSERT INTO Users VALUES (@Name, @Email, @Password)
END

我如何以原子方式执行此操作，以便保证总行数少于 100 万，但同时我进行最小锁定以防止阻塞其他事务？

针对这种情况有什么解决方案/最佳做法吗？

Answer 1

您可以创建一个 SELECT 来检查用户数并检索新用户的数据，然后使用该选择插入到表中:

INSERT INTO users (
  name, email, [password]
)
SELECT
  'newUser' AS name
  , 'email@example.com' AS email
  , 'fsfsfs' AS [password]
FROM
  users
HAVING
  COUNT(*) < 100000;

SELECT 语句在数据库中少于 100000 个用户时返回 1 行，在 100000 或更多用户时返回 0 条记录。

整个语句 (INSERT..SELECT) 在每种情况下都是有效的，但是当 SELECT 部分返回 0 行时，INSERT 不会插入任何内容。

使用SERIALIZABLE事务级别，可以保证并发写入不会相互干扰。 COUNT(*) 使用最有效的索引/键来计算行数，这意味着锁定时间将是最短的。

由于整个操作是在一条语句中完成的，因此可以防止在执行 SELECT 和 INSERT 之间插入

SQL Fiddle demo