Concurrency procedure calls from different back-ends

Question

I have a procedure that increments a counter in a table. This procedure increments a value when it is called from a back-end. The problem is when this procedure is called in parallel by several Java consumers, as shown below:

https://i.sstatic.net/liOfL.png

And the content of the procedure simply adds one number to a column to check if all known messages in a queue are complete.

`

CREATE PROCEDURE [dbo].[INCREMENT_TABLE_COUNT]
(
  @id_entry INT
)

AS BEGIN

 DECLARE @TOTAL INT = 0
 DECLARE @CREATED INT = 0

  -- I've tried using this set in isolation, but sometimes the select below gets the same number and doesn't increment correctly
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
  
  BEGIN TRAN
  BEGIN
          SELECT
          @TOTAL = Total_Accounts
          ,@CREATED = Created_Accounts
          FROM table_count col
          WHERE col.first_col =  @id_entry

          IF @CREATED < @TOTAL
          BEGIN
          BEGIN TRANSACTION;

            SET @CREATED = @CREATED + 1 -- increment a value based on the select above, 
                                                -- I suspects that sometimes it gets the same select value twice

            UPDATE table_count SET
              Created_Accounts = @CREATED 
            WHERE col.first_col = @id_entry 
         COMMIT TRANSACTION;
         END

  END
  COMMIT

  SELECT
  , @TOTAL as Total_Accounts
  , @CREATED as Created_Accounts

  RETURN
END

`

I expect that when this procedure is called by several back-ends, It holds the lock on the table and guarantee the transaction creating an output like this:

Total_Accounts	Created_Accounts
2000	1
...	...
2000	1997
2000	1998
2000	1999
2000	2000

But when I get the application Log, calling the procedure, I get this output below, the created account duplicates, indicating that the select inside the procedure is getting the same value twice and increment the value.

Total_Accounts	Created_Accounts
2000	1
...	...
2000	1997
2000	1998
2000	1998
2000	1999

I checked that I had 2000 messages, and the procedure was called exactly 2000 times but the total of created account sometimes doesn't achieve 2000.

To overcome this, I've tried Setting the SET TRANSACTION ISOLATION LEVEL inside the procedure. But the READ COMMITTED didn't work. I also try setting the BEGIN and the COMMIT in different parts of the procedure to try to commit the update, before the next select made by the Procedure.

What's the best way to avoid this update problem ?

Answer 1

This is a classic race condition since the values can be changed by another session between the initial SELECT and subsequent UPDATE. Although this could be addressed with a more restrictive isolation level and/or hints, a single atomic auto-commit UPDATE statement will address the concurrency issue and, IMHO, easier to understand and maintain.

CREATE PROCEDURE [dbo].[INCREMENT_TABLE_COUNT]
  @id_entry INT
AS
UPDATE table_count 
SET Created_Accounts += 1 
OUTPUT inserted.Total_Accounts, inserted.Created_Accounts 
WHERE 
    first_col = @id_entry 
    AND Created_Accounts < Total_Accounts;