I'm designing an inventory system with Entity Framework and SQL Server. This app will be used by thousands of users. How do I design an app/database that will allow users to update the same inventory data at the same time? At the moment, for consistency, I use row versioning, but this often causes "Update conflict" errors.
Read more about Retry Pattern (here is my post about it). With it you can detect conflicts and make a desicion what to do (rewrite, merge or throw error to user).
Example of usage:
var retryCount = 3;
var currentRetry = 0;
using (var context = new DbContext(ConnectionString))
{
var user = context.Set<User>().First(o => o.Id == 1);
user.Login = "newuserlogin";
do
{
try
{
currentRetry++;
context.SaveChanges();
break;
}
catch (DbUpdateConcurrencyException ex) when (currentRetry <= retryCount)
{
//conflict detected
var entry = ex.Entries.Single();
//rewrite values from database
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
}
} while (true);
}