How to handle unique constraint errors

I received a question recently about the topic I wanted to cover for a long time already:

How to handle unique constraint violations?

Let’s say that a controller needs to change the user email. A typical code would look something like this:

public class UserController
{
    public string ChangeEmail(int userId, string newEmail)
    {
        Result<Email> emailResult = Email.Create(newEmail);
        if (emailResult.IsFailure)
            return "Email is incorrect";

        User existingUser = _userRepository.GetByEmail(newEmail);
        if (existingUser != null && existingUser.Id != userId)
            return "Email is already taken";

        User user = _userRepository.GetById(userId);
        user.Email = emailResult.Value;
        _userRepository.Save(user);

        return "OK";
    }
}

This code works fine in most cases, but what if there are two simultaneous requests that try to change two users' emails to the same one?

It would lead to a race condition where the email uniqueness check will pass for both of these requests and the controller will try to update both of the users.

Of course, if you use a relational database, then you can protect your data integrity by introducing a unique index for the User.Email column that would reject one of the updates.

But still, even though the database will be fine, one of the requests will result in an exception that would lead to a 500 (internal server) error. Of course, the correct behavior here is to return a 400 (bad request) error, not 500.

How to best handle this situation?

First of all, you need to take into account how often such race conditions occur. If they are rare enough to the point that occasional 500 errors don’t bother your clients too much, then do nothing. Some errors are just not worth fixing.

If they do bother your clients, then you need to catch these exceptions and transform them into 400 responses.

There are two main rules when it comes to exception handling:

Catch expected exceptions at the lowest level of the call stack and then convert them into Result instances.
Catch unexpected exceptions at the highest level of the call stack and then log them and end the current operation.

Here’s an article to read more about these guidelines: Exceptions for flow control.

The exception about unique constraint violation is expected, so you need to catch it at the lowest level possible. Where is it, exactly?

If you use an ORM, such as NHibernate or Entity Framework, then it’s going to be ISession.Dispose() (or ISession.Flush()) and DbContext.SaveChanges() respectively.

Create your own wrapper on top of these methods and convert specific exceptions into Result instances.

Notice the word specific. You shouldn’t just bluntly convert all exceptions coming from the database into results — only those you expect. It means that you need to check what exceptions you’ll get from the database in your particular use case and somehow distinguish them from all other potential exceptions.

Let’s say that this is the exception you are getting when trying to insert a duplicate user email:

Msg 2627, Level 14, State 1, Line 2 Violation of UNIQUE KEY constraint 'IX_User_Email'. Cannot insert duplicate key in object 'dbo.User'. The duplicate key value is (email@gmail.com). The statement has been terminated.

The key part here is the constraint name (IX_User_Email). This constraint is what you need to look for in your wrapper:

// UnitOfWork is a wrapper on top of EF Core's DbContext
public class UnitOfWork
{
    public Result SaveChanges()
    {
        try
        {
            _context.SaveChanges();
            return Result.Success();
        }
        catch (Exception ex)
        {
            if (ex.Message.Contains("IX_User_Email"))
                return Result.Failure("Email is already taken");

            // Other expected exceptions go here

            throw;
        }
    }
}

This code will only catch email duplication errors and will re-throw all others. Note that if you have more then one database exception you want to catch, you can map them all into proper errors in this wrapper.

This is what the controller will look like after this modification:

public class UserController
{
    public string ChangeEmail(int userId, string newEmail)
    {
        Result<Email> emailResult = Email.Create(newEmail);
        if (emailResult.IsFailure)
            return "Email is incorrect";

        User existingUser = _userRepository.GetByEmail(newEmail);
        if (existingUser != null && existingUser.Id != userId)
            return "Email is already taken";

        User user = _userRepository.GetById(userId);
        user.Email = emailResult.Value;
        _userRepository.Save(user);

        Result result = _unitOfWork.SaveChanges();
        if (result.IsFailure)
            return result.Error;

        return "OK";
    }
}

Notice the additional SaveChanges method call. It might look excessive (since the controller already calls _userRepository.Save(user)), but it’s not. Declaring database changes and then committing these changes are two separate decisions, and should be processed as such.

This is similar to database transactions: enlisting a data change in the current transaction and then committing or rolling that transaction back are the decisions you want to make separately.

Also notice that with this implementation, all unexpected errors are still re-thrown and ultimately converted into 500 responses. You don’t want to catch unexpected exceptions as if they are something you can deal with — those should still manifest themselves as internal server (500) errors.

--Vlad

https://enterprisecraftsmanship.com

Enjoy this message? Here are more things you might like:

Workshops — I offer a 2-day workshop for organizations on Domain-Driven Design and Unit Testing. Reply to this email to discuss.

Unit Testing Principles, Patterns and Practices — A book for people who already have some experience with unit testing and want to bring their skills to the next level.
Learn more »

My Pluralsight courses — The topics include Unit Testing, Domain-Driven Design, and more.
Learn more »