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

2021-09-10 00:00:00 sql tsql sql-server

我使用的是 SQL Server 2012.

I am using SQL Server 2012.

我有存储用户的表.允许的最大用户数为 100 万.我有一个 sproc 来注册用户.当我插入时，我想确保用户表中的总行数不会超过 100 万.我更喜欢使用允许最大并发的方法.

I have table where I store the users. The max count of users allowed is 1 million. I have a sproc where I am registering users. When I insert I want to make sure that the total number of rows in the users table will not exceed 1 million. I would prefer to use a method which allows maximum concurrency.

我相信我可以使用 ISOLATION LEVEL 为 SERIALIZABLE 的事务，然后首先计算行数，如果总计数小于 100 万则插入.我的理解是 SERIALIZABLE 限制性很强，会随着并发的增加导致性能下降.

I believe I can use a transaction with an ISOLATION LEVEL of SERIALIZABLE and then first count the number of rows and insert if the total count is less than 1 million. My understanding is that SERIALIZABLE Is very restrictive and will cause performance degradation as concurrency increases.

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

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

How can I do this atomically so that the total row count is guaranteed to be less than 1 million, but at the same time I do minimal locking to prevent blocking other transactions?

针对这种情况的任何解决方案/最佳做法?

Any solution/best practices for this situation?

推荐答案

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

You can create a SELECT to check the count of users and retrieve the new user's data and use that select to insert into the table:

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 时返回一行，当用户数大于 100000 时返回 0 条记录.

The SELECT statement will return one row when there are less than 100000 users in the database and 0 record when there are 100000 users or more.

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

The whole statement (INSERT..SELECT) will be valid in each cases, but when the SELECT part returns 0 row, the INSERT will not insert anything.

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

With the SERIALIZABLE transaction level, it is guaranteed that the concurent writes can not interfere with eachother. The COUNT(*) uses the most efficient index/key to count the rows, which means, the lock time will be the minimal.

由于整个操作在一个语句中完成，这可以防止在 SELECT 和 INSERT

Since the whole action is done in one statement, this prevents an insertation between the execution of the SELECT and INSERT

SQL Fiddle 演示

相关文章