“Buy Items” with SQL Transaction in EF Core, if user send many requests, items are bought even if they can’t afford it

I’m trying to make a game where the user can buy items with ingame coins. The coins and the items are stored in a SQL database, and user should (obviously) only be able to buy items that they can afford.

The Problem with this code is that if the user spams many requests at the same time, he gets more items than he can afford since the next request is sent before the previous one is done updating the database.

How do I prevent this “spam posting bug”?

public async Task<ActionResult<UserProfile>> BuyItem(ShopItem item)
{
    try
    {
        //Begin Transaction
        using var transaction = _context.Database.BeginTransaction();
        var user = await _context.UserProfiles.FirstOrDefaultAsync(u => u.Name == User.Identity.Name);
        await _context.Database.OpenConnectionAsync();
        
        //If user can afford the item
        if (user.Coins >= item.Price)
        {
            //Decrease money from user
            user.Coins -= item.Price;
            _context.Entry(user).State = EntityState.Modified;
            await _context.SaveChangesAsync();

            //Create new item in database that belongs to the user who bought it
            var userItem = new UserItem()
            {
                ShopItemId = item.Id,
                UserProfileId = user.Id
            };
            _context.UserItems.Add(userItem);
            await _context.SaveChangesAsync();
        }
        else
        {
            //Throw exception, this should rollback the transaction?
            throw new Exception();
        }
        //End Transaction
        _context.Database.CloseConnection();
        transaction.Commit();

        return new JsonResult(user);
    }
    catch (Exception ex)
    {
        throw;
    }
}

Answer

_context.Database.BeginTransaction(IsolationLevel.Serializable)

Specifies the following:

• Statements cannot read data that has been modified but not yet committed by other transactions.

• No other transactions can modify data that has been read by the current transaction until the current transaction completes.

• Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Docs: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15