Sql-server – SQL Insert with soft row limit in stored procedure

concurrencysql serverstored-procedures

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

Best Answer

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.

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

The error in the EXEC part of the INSERT-EXEC statement is leaving your transaction in a doomed state.

If you PRINT out XACT_STATE() in the CATCH block it is set to -1.

Not all errors will set the state to this. The following check constraint error goes through to the catch block and the INSERT succeeds.

ALTER PROCEDURE test -- or create
AS
  BEGIN try
      DECLARE @retval INT;

      DECLARE @t TABLE(x INT CHECK (x = 0))

      INSERT INTO @t
      VALUES      (1)

      SET @retval = 0;

      SELECT @retval;

      RETURN( @retval );
  END try

  BEGIN catch
      PRINT XACT_STATE()

      PRINT ERROR_MESSAGE();

      SET @retval = -1;

      SELECT @retval;

      RETURN( @retval );
  END catch;

Adding this to the CATCH block

 IF (XACT_STATE()) = -1
BEGIN
    ROLLBACK TRANSACTION;
END;

Doesn't help. It gives the error

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

I don't think there is any way of recovering from such an error once it has happened. For your specific use case you don't need INSERT ... EXEC anyway though. You can assign the return value to a scalar variable then insert that in a separate statement.

DECLARE @RC INT;

EXEC sp_executesql
  N'EXEC @RC = test',
  N'@RC INT OUTPUT',
  @RC = @RC OUTPUT;

INSERT INTO @t
VALUES      (@RC)

Or of course you could restructure the called stored procedure so that it doesn't raise that error at all.

DECLARE @RetVal INT = -1

IF OBJECT_ID('PrintMax', 'P') IS NULL
  BEGIN
      EXEC('create procedure PrintMax as begin print ''hello world'' end;')

      SET @RetVal = 0
  END

SELECT @RetVal;

RETURN( @RetVal );

Sql-server – Oracle GoldenGate add trandata errors

I found out what the problem is, it seems that GoldenGate doesn't work with SQL Express. The server I was connecting to is SQL Express, I'll need to use the Enterprise Edition.

Best Answer

Related Solutions

Handling Exceptions in Stored Procedures with Insert-Exec Blocks

Sql-server – Oracle GoldenGate add trandata errors

Related Question