2

I am using SQL Server 2012.

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.

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

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?

Pradeep
  • 731
  • 1
  • 7
  • 13

1 Answers1

9

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;

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.

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.

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.

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

SQL Fiddle demo

Pred
  • 8,789
  • 3
  • 26
  • 46
  • Whow. That is a really smart "abuse" of that form - I am seriously impressed. Marking this to remember it when I need it. Great answer. – TomTom Oct 19 '14 at 08:45
  • @Pred - Do I need to use the SERIALIZABLE isolation level for this to work correctly? – Pradeep Oct 19 '14 at 17:28
  • `REPEATABLE READ` could be enough if serializable is too strict. In real world scenarios, when the users are registering in a long time period, this is not really matters, so it could be `READ COMMITTED`, but if this part of your app requires this function and it should work as strictly as it can, than the DBE should work in it's most strict mode when it runs this code. – Pred Oct 19 '14 at 17:45
  • I wonder if `SERIALIZABLE` is not necessary in order to "prevent an insertation between the execution of the `SELECT` and `INSERT`". This Q&A seem to contradict: http://stackoverflow.com/questions/1994771/in-tsql-is-an-insert-with-a-select-statement-safe-in-terms-of-concurrency – Kuba Wyrostek Mar 15 '17 at 22:48
  • Well, `TABLOCKX` will place an exclusive lock on the table, `SERIALIZABLE` will do so too since we have no filters, therefore the whole table will be used and locked until the end of the transaction. This is similar to the `TABLOCKX`, `HOLDLOCK` pair. Moreover `HOLDLOCK` is deprecated if I recall it correctly. – Pred Mar 16 '17 at 09:32