Conditionally inserting records into a table in a multi-threaded environment based on counting

Question

I am writing a T-SQL stored procedure that conditionally adds a record to a table only if the number of similar records is below a certain threshold, 10 in the example below. The problem is that this will be launched from a web application, so it will run on multiple threads, and I need to make sure that the table never contains more than 10 similar entries.

The main point of the procedure:

BEGIN
  DECLARE @c INT
  SELECT @c = count(*)
    FROM foo
    WHERE bar = @a_param

  IF @c < 10 THEN
    INSERT INTO foo
      (bar)
    VALUES (@a_param)
  END IF
END

I think I could solve any potential concurrency problems by replacing the select statement:

SELECT @c = count(*) WITH (TABLOCKX, HOLDLOCK)

But I'm curious if there are any methods, other than locking, for managing concurrency problems in T-SQL

Answer 1

sp_getapplock. SQL- .

:

CREATE PROC MyCriticalWork(@MyParam INT)      
AS
    DECLARE @LockRequestResult INT
    SET @LockRequestResult=0

    DECLARE @MyTimeoutMiliseconds INT
    SET @MyTimeoutMiliseconds=5000--Wait only five seconds max then timeouit

    BEGIN TRAN

    EXEC @LockRequestResult=SP_GETAPPLOCK 'MyCriticalWork','Exclusive','Transaction',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN

        /*
        DO YOUR CRITICAL READS AND WRITES HERE
        */

        --Release the lock
        COMMIT TRAN
    END ELSE
        ROLLBACK TRAN               

Answer 2

SERIALIZABLE. , . , . SQL : , . - . Retry .

Answer 3

. ? .

  insert into [Table_1] (ID, fname, lname)
  select 3, 'fname', 'lname' 
    from [Table_1] 
   where ID = 3 
  having COUNT(*) <= 10  

,

• Non-3NF data
  Must start any design with an appropriate data model.
• Why exclude table locking?
  This may be the best in the best way.
• Really, what are the odds?
  Even without blocking, you must have two in the amount of 9 points at a time. Even then, it would stop at 11. Is 10 an absolute hard number?