Ensure concurrent (money) transactions in Entity Framework?

Question

Assume I have an account_profile table, which has Score field that is similar to an account's money (the database type is BIGINT(20) and the EntityFramework type is long, because I don't need decimal). Now I have the following function:

public long ChangeScoreAmount(int userID, long amount)
{
    var profile = this.Entities.account_profile.First(q => q.AccountID == userID);

    profile.Score += amount;

    this.Entities.SaveChanges();

    return profile.Score;
}

However, I afraid that when ChangeScoreAmount are called multiple times concurrently, the final amount won't be correct.

Here are my current solutions I am thinking of:

• Adding a lock with a static locking variable in the ChangeScoreAmount function, since the class itself may be instantiated multiple times when needed. It looks like this:

  public long ChangeScoreAmount(int userID, long amount)
  {
      lock (ProfileBusiness.scoreLock)
      {
          var profile = this.Entities.account_profile.First(q => q.AccountID == userID);
  
          profile.Score += amount;
  
          this.Entities.SaveChanges();
  
          return profile.Score;
      }
  }
  

The problem is, I have never tried a lock on static variable, so I don't know if it is really safe and if any deadlock would occur. Moreover, it may be bad if somewhere else outside this function, a change to Score field is applied midway.

OK this is no longer an option, because my server application will be run on multiple sites, that means the locking variable cannot be used

• Creating a Stored Procedure in the database and call that Stored procedure in the function. However, I don't know if there is an "atomic" way to create that Store Procedure, so that it can only be called once at a time, since I still need to retrieve the value, changing it then update it again?

I am using MySQL Community 5.6.24 and MySQL .NET Connector 6.9.6 in case it matters.

NOTE My server application may be runned on multiple server machines.

Answer 1

You can use sql transactions with repeatable read isolation level instead of locking on the application. For example you can write

public long ChangeScoreAmount(int userID, long amount)
{
    using(var ts = new TransactionScope(TransactionScopeOption.RequiresNew, 
        new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead })
    {
      var profile = this.Entities.account_profile.First(q => q.AccountID == userID);

      profile.Score += amount;

      this.Entities.SaveChanges();

      ts.Complete();

      return profile.Score;
    }    
}

Transaction garantees that accountprofile record will not changed in db while you aren't commit or rollback.