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;
}
}
Advertisement
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.