How to make a row lock for table use as counters?

Question

I have an API who makes transaction using differents providers and for each provider, I handle an internal counter in a table. The problem comes because we will receive more traffic and we have a transaction with repeated numeration.

The query I use for the counter is the next:

public async Task<int> GetNumeration(int id)
{
    var providerNumerationDb = await _dbContextEf
        .providerNumeration
        .SingleOrDefaultAsync(x => x.providerId == id);

    if (providerNumerationDb == null)
    {
        providerNumerationDb = new ProviderNumeration
        {
            providerId = id,
            number = 1,
        };
        await _dbContextEf.AddAsync(providerNumerationDb);
    }
    else
    {
        providerNumerationDb.Number += 1;
    }

    await _dbContextEf.SaveChangesAsync(0);
    _dbContextEf.Entry(providerNumerationDb).State = EntityState.Detached;

    return providerNumerationDb.Number;
}

I want to know the best strategy to make a Rowlock with no performance consequences or another solution. If you think the solution is better using StoredProcedure for example or another way, it is valid also, we can change the implementation.

Answer 1

Below is an example using a SERIALIZABLE transaction. A rowlock on the updated key is acquired for the update and, when the ID doesn't already exist, a key range lock is acquired for the insert. The latter will limit concurrency for other inserts within the range but probably won't be a concern since the transaction will commit immediately, releasing the locks.

CREATE TABLE dbo.Provider(
      ProviderId int NOT NULL CONSTRAINT PK_Provider PRIMARY KEY
    , Number int NOT NULL
);
GO

CREATE PROCEDURE dbo.GetNextProviderNumber
    @ProviderID int
AS
SET NOCOUNT, XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @Number int;
BEGIN TRY

    BEGIN TRAN;

        UPDATE dbo.Provider
        SET @Number = Number += 1
        WHERE ProviderID = @ProviderID;

        IF @@ROWCOUNT = 0
        BEGIN
            SET @Number = 1;
            INSERT INTO dbo.Provider(ProviderID, Number)
                VALUES(@ProviderID, @Number);
        END

    COMMIT;

    SELECT @Number AS Number;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;

END CATCH;
GO