Postgresql – Handling concurrent gift card purchases in ASP.NET Core API without causing database concurrency issues

I’m working on a gift card system where I have two main database tables:
GiftCards and ActivationCodes. Each gift card can have many pre-generated activation codes, and after a user purchases a gift card, they should receive one of the available activation codes.

// ActivationCode Entity
public class ActivationCode : BaseEntity
{
    public Guid Id { get; set; }
    public string Code { get; set; }
    public bool isUsed { get; set; } = false;
    public DateTime? ExpiresOn { get; set; }
    public bool IsExpired => DateTime.UtcNow >= ExpiresOn;

    public Guid GiftCardId { get; set; }
    public GiftCard GiftCard { get; set; }
    
    [Timestamp]
    public uint RowVersion { get; set; } // For Optimistic Concurrency Control
}

// GiftCard Entity
public class GiftCard : BaseEntity
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public decimal Value { get; set; }

    public List<ActivationCode> ActivationCodes { get; set; }
}

//Purchase Gift Card
public async Task<GeneralResult<GiftCardPurchaseResult>> PurchaseGiftCard(
    Guid giftCardId, string userId)
{
    // 1. Retrieve the gift card information from the database
    // (checks if it's valid and available).

    // 2. Attempt to get an available activation code that hasn't been
    // used or expired, and isn't deleted.
    var getAvailableActivationCode = await _activationCodeRepository
        .GetTableNoTracking()
        .Where(gd => gd.GiftCardId == giftCardId && !gd.isUsed && !gd.IsDeleted && 
                     (DateTime.UtcNow < gd.ExpiresOn || gd.ExpiresOn == null))
        .FirstOrDefaultAsync();

    // If no activation codes are available, return an error indicating
    // the gift card is out of stock.
    if (getAvailableActivationCode is null)
    {
        return GeneralResult<GiftCardPurchaseResult>
            .Failure("No activation codes are currently available "
                + "for this gift card.",
            HttpStatusCode.BadRequest,
            new List<string> { GiftCardTransactionsErrorCode
                .ACTIVATION_CODES_NOT_AVAILABLE });
    }

    // 3. Retrieve the user's wallet balance and check if the balance
    // is sufficient for the purchase.

    using (var transaction = await _dataContext.Database.BeginTransactionAsync())
    {
        try
        {
            // 4. Debit the user's wallet by deducting the gift card price.

            // 5. Mark the activation code as "used" by setting isUsed to true.
            getAvailableActivationCode.isUsed = true;

            // Update the activation code record in the database.
            await _activationCodeRepository.UpdateAsync(getAvailableActivationCode);

            // 6. Create a new entry in the GiftCardPurchases table to record
            // the purchase details.

            // Commit the transaction to apply changes to the database.
            await transaction.CommitAsync();

            // 7. Return a successful result with the purchase information.
            return GeneralResult<GiftCardPurchaseResult>.Success(purchaseResultInfo,
                "Gift card purchased successfully.");
        }
        catch (DbUpdateConcurrencyException ex)
        {
            // Handle concurrency issues (e.g., another transaction might have
            // updated the same activation code).
        }
    }
}

In my API (ASP.NET Core web API), when a user purchases a gift card, they are assigned one of the available, unused activation codes (isUsed == false and !IsExpired). The problem arises when concurrent requests for the same gift card result in multiple users being assigned the same activation code. This causes a DBConcurrencyException due to my optimistic concurrency control setup (RowVersion).

The main challenge is that the system doesn’t need to wait for a specific activation code to be freed, as there are other available codes. However, I’m running into issues with concurrent transactions attempting to modify the same activation code.

What I’ve tried:

Pessimistic Locking: This isn’t ideal in my case since there are other available activation codes, and we don’t want the system to wait unnecessarily for a specific row to be unlocked.

Optimistic Concurrency Control: I’ve implemented optimistic concurrency using a RowVersion field. However, when two users try to select and mark the same activation code as used, the second transaction fails with a DbUpdateConcurrencyException. This forces retries, but I would rather avoid these concurrency conflicts altogether by ensuring that different codes are selected upfront.

Question :
How can I ensure that when concurrent users are purchasing the same gift card, they are always assigned different available activation codes without running into database concurrency issues? Is there a better pattern to handle this scenario where multiple activation codes are available?

Any insights into patterns or approaches to handle this kind of concurrency issue would be greatly appreciated!