仅当总计数小于阈值时向 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.

由于整个操作在一个语句中完成,这可以防止在 SELECTINSERT

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

SQL Fiddle 演示

相关文章