1

I have a table ([Label]) which i would like to enforce a soft limit on the number of rows a company can insert (stored procedure code below) If this stored procedure is being called frequently (e.g. a few times per second by different users), is it possible that it will insert a row over the limit due to concurrency issue? Should i use LOCK or TRANSACTION?

CREATE PROCEDURE [dbo].[admin_AddLabel]
@companyId INT,
@label NVARCHAR(50)
AS
DECLARE @limit INT = 10
DECLARE @count INT = (SELECT COUNT(1) FROM Label WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
INSERT INTO Label (Label, CompanyId)
VALUES (
  @label,
  @companyId
)
RETURN 0
END
  • This stored procedure is invalid. Assuming it's pseudo code, how can you even enforce a 10 row limit? I can just call it 100 times in a row and it will insert 100 records. What 'domain' is the 10 row limit within? The stored procedure call? That proc can only ever insert one row at a time, but you can call it 100 times with no issue – Nick.McDermaid May 10 '17 at 10:45
  • @Nick.McDermaid There is a condition which compares the limit with the current count – maxi C May 10 '17 at 23:38
  • OK now that you have edited it it makes sense. Rather than using IF then INSERT, I suggest you INSERT a SELECT that checks the condition in the select. I'll post an answr – Nick.McDermaid May 10 '17 at 23:53
1

DECLARE @count INT = (SELECT 1 FROM Label WHERE CompanyId = @companyId)

This makes little sense. It should be SELECT COUNT(...)

is it possible that it will insert a row over the limit due to concurrency issue?

Yes. More than just one row. If N concurrent users will reach the count at the same point and count limit -1, then all N will proceed and insert thus you will exceed by N-1.

Should i use LOCK or TRANSACTION?

It is possible, is very difficult to get right, and will severely impact performance. And will be easily defeated by inserts that do not go through this stored procedure (data always outlives the app), by updates that modify the CompanyId and so on and so forth.

BEGIN TRANSACTION
DECLARE @count INT = (
  SELECT COUNT(*) 
    FROM Label WITH (UPDLOCK)
    WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
    INSERT INTO Label (Label, CompanyId)
    VALUES (@label, @companyId)
END
COMMIT

I would strongly advise against doing something like this. Revisit the requirements, a 'soft limit' is very hard to get right in a relational model.

I recommend you read also Enforcing Complex Constraints with Indexed Views, maybe you find something to inspire you.

  • Thanks for picking up my human error, meant to do COUNT(1). Also the helpful article too! – maxi C May 10 '17 at 23:17
  • @Remus Rusanu, Sorry to ask the below question here. I have no idea to design the tables in a better manner. Kindly have a look. stackoverflow.com/questions/46285924/…. Please suggest me the better solution to handle that scenario. – RGS Sep 21 '17 at 11:37
1

Resist the urge to do things procedurally. This one only inserts a record if at the time the count < limit.

Your original query might in extreme cases allow someone to insert over the limit.

CREATE PROCEDURE [dbo].[admin_AddLabel]
@companyId INT,
@label NVARCHAR(50)
AS
BEGIN

DECLARE @limit INT = 10

INSERT INTO Label (Label, CompanyId)
SELECT @label, @companyId 
WHERE 
(SELECT COUNT(*) FROM Label WHERE CompanyId = @companyId) < @limit

RETURN 0
END
  • Thanks! That is a good approach. I wonder if the sql engine still split this statement into two steps, where the first step select the data into a temp table. If not, i think this will solve my problem. – maxi C May 11 '17 at 2:19
  • I can't say for certain but I think under default isolation levels (read committed) it can't break your rules. Hopefully some other egghead who knows more about isolation levels can comment here! – Nick.McDermaid May 11 '17 at 2:26
  • This is no different, isolation and concurrency wise, from the OP. It can and it will go over limit. Is true that you can argue that the race condition window is shorter. – Remus Rusanu May 11 '17 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.