How to handle concurrent updates in C# dotnet webapi without EF

Question

I have a claim voucher function that needs to get the first available code from a list of codes and 'claim it' by changing its status. The expected behavior of this function is that if it is called concurrently, each call should receive a different voucher code.

However, when I make 2 concurrent calls to this function, I get a deadlock error (the system automatically terminates 1 process and goes on with the other).

I have read that having my isolation level at Serializable is not recommended and is very likely causing the deadlock error. However, when the isolation level is set to anything else, and concurrent calls are made, all calls return the same voucher code.

public async Task<ClaimVoucherResponse> ClaimVoucher(ClaimVoucherRequest req)
        {
            using var connection = new SqlConnection(MSSQLConnectionString);
            await connection.OpenAsync();
            var transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
            var cmd = connection.CreateCommand();
            cmd.Connection = connection;
            cmd.Transaction = transaction;

            try
            {
                // this function makes a query like "SELECT TOP 1 * FROM table WHERE voucherId={voucherId} AND status=1"
                var voucherCode = await GetAvailableCode(voucherId, cmd);

                // this function makes a query like "UPDATE table SET status={status} WHERE voucherCodeId={voucherCodeId}"
                await UpdateVoucherCode(voucherCode.Id, new SetVoucherCodeRequest
                {
                    Status = 2,
                }, cmd);
                transaction.Commit();
                return new ClaimVoucherResponse
                {
                    VoucherCode = voucherCode.VoucherCode,
                };
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  ----- Message: {0}", ex.Message);
                transaction.Rollback();
            }
        }

What I have tried:

If I set the isolation level to Serialization, I get a deadlock error

If I set the isolation level to anything else, the function returns the same voucher code to every concurrent call

Answer 1

Not really an answer, but more of a demonstration that the simple update is thread safe (anecdotally at least). The demo set up is as follows.

Create a voucher table and populate with a million unallocated vouchers, and a log table we'll use to check for duplicate allocations:

DROP TABLE IF EXISTS dbo.Voucher;
CREATE TABLE dbo.Voucher (VoucherID INT IDENTITY NOT NULL, SessionID INT NULL);
GO
INSERT dbo.Voucher (SessionID)
SELECT TOP 1000000 NULL
FROM sys.all_objects AS a
CROSS JOIN sys.all_objects AS a2;

DROP TABLE IF EXISTS dbo.VoucherLog;
CREATE TABLE dbo.VoucherLog(VoucherID INT NOT NULL, SessionID INT NOT NULL);

Then in as many sessions as you please (I chose 10) run the following code:

SET NOCOUNT ON;
WHILE EXISTS (SELECT 1 FROM dbo.Voucher AS v WHERE v.SessionID IS NULL)
BEGIN

    UPDATE  v
    SET     SessionID = @@SPID
    OUTPUT inserted.VoucherID, inserted.SessionID INTO dbo.VoucherLog (VoucherID, SessionID)
    FROM    (SELECT TOP (1) v.VoucherID, v.SessionID FROM dbo.Voucher AS v WHERE v.SessionID IS NULL ORDER BY v.VoucherID) AS v;

END

You can then check to see if the same voucher ID has been updated twice:

SELECT VoucherID
FROM dbo.VoucherLog --WITH (NOLOCK) -- Uncomment if you want to check while other threads are running
GROUP BY VoucherID
HAVING COUNT(*) > 1

I was running on my localdb and it quickly slowed my machine down so I didn't run until all vouchers had been allocated, but with 10 sessions simultaneously updating for 5 minutes, there was not one duplicate.

If you need to do additional updates/inserts on other tables then do so after the voucher code has been returned and the lock on the voucher table released, at worse if you need to set voucher back to available you could set the status back to 1 and it would be picked up at the next available opportunity