I have the following scenario..

1. Employee record is created
2. NextPayrollNumber is read from record in database table (settings table)
3. Number is incremented by 1 and added as PayrollNumber to Employee record, as well as overwriting current NextPayrollNumber
4. Employee Record is saved to database (employee table)

I need to ensure that two Employee records won't have the same number and I have done some searching and it looks like concurrency issues are usually handled with a Concurrency Token and doing concurrency exception handling in the DBContext. But this involves adding another column to the settings table to store rowversion and adding code to the dbcontext that would only be used for this one requirement and the rest of the application doesn't need.

Are there other approaches to handling this? I would have liked to add a unique constraint to the database table (but the column will have null values) or use a sequence but the value needs to be based on the NextPayrollNumber that will be configurable by an end-user.

CodePudding user response：

Given the payroll # is not the PK for the row (using an in-built identity) and you don't want to derive a payroll number from the PK identity, Then my suggestion to be safe rather than worrying about the exception case of 2 inserts happening together, handle the exception with a retry having a unique constraint on the Payroll #. Basically populate the payroll # and save the record as quickly as possible, and if you hit a duplicate exception (which should be rare) handle it by fetching a new payroll # from your settings (ensuring you reload the setting entity or fetch from DB not a cached row) and save again, retrying if necessary. If the next # comes back the same as what you retrieved then you have a bigger problem with the insert and can bail with an exception message.

var settings = _context.Settings.Single(); // However this is loaded, whether single row or row per Tenant...

var customer = new Customer 
{
   // populate values...
   PayrollNumber = settings.NextPayrollNumber  ;
};
int retryCount = 0;
bool complete = false;
while(retryCount < 5 && !complete)
{
     try
     {
        _context.SaveChanges();
        complete = true;
     }
     catch (SqlException ex)
     {
        var constraintErrorNumbers = new[] {547, 2601, 2627}; // SQL codes around constraint violations.
        if (constraintErrorNumbers.Contains(ex.Number)
        {
            _context.Entry(settings).Reload(); //Refresh the settings from the DB.
            int currentPayrollNumber = customer.PayrollNumber;
            customer.PayrollNumber = settings.NextPayrollNumber  ;
            if(customer.PayrollNumber == currentPayrollNumber)
                throw; // It wasn't the payroll number that was duplicated because the sequence hasn't changed.
            retryCount  ;
        }
        else
            throw;
     }
}

You will most likely need to catch something like an EF InsertException or UpdateException rather than SqlException and inspect the InnerException which should be the SqlException.

This should update the NextPayrollNumber in the settings with a successful save of the new customer.

Typically I wouldn't recommend keeping a Sequence in a table for something like a payroll number but generating/selecting a "should be unique" value like a random number, snowflake, or hash etc. to build and validate a new payroll number. The same retry logic would need to apply to handle the rare case of a duplication but this would not be relying on coordinating inserts to one sequence.