What happens if 2 or more people update a record at the exact same time?

Question

I'm using NHibernate with the version property that automatically increments every time my aggregate root is updated. What happens if 2 or more people update the same record at the exact same time?

Also, how would I test this?

Note that this isn't a situation I've been in, just wondering.

Answer 1

What's Atomic, and What's Not

As the others have stated, updates in SQL Server are atomic operations. However, when updating data with NHibernate (or any O/RM), you typically first select the data, make your changes to the object, then update the database with your changes. That sequence of events is not atomic. Even if the select and update were performed within milliseconds of each other, the chance exists for another update to slip in the middle. If two clients fetched the same version of the same data, they could unwittingly overwrite each-other's changes if they assumed that they were the only ones editing that data at that time.

Problem Illustration

If we didn't guard against this concurrent-update scenario, weird things could happen - sneaky bugs that shouldn't seem possible. Suppose we had a class that modeled the state changes of water:

public class BodyOfWater
{
    public virtual int Id { get; set; }
    public virtual StateOfMatter State { get; set; }

    public virtual void Freeze()
    {
        if (State != StateOfMatter.Liquid)
            throw new InvalidOperationException("You cannot freeze a " + State + "!");
        State = StateOfMatter.Solid;
    }

    public virtual void Boil()
    {
        if (State != StateOfMatter.Liquid)
            throw new InvalidOperationException("You cannot boil a " + State + "!");
        State = StateOfMatter.Gas;
    }
}

Let's say the following body of water is recorded in the database:

new BodyOfWater
{
    Id = 1,
    State = StateOfMatter.Liquid
};

Two users fetch this record from the database at roughly the same time, modify it, and save the changes back to the database. User A freezes the water:

using (var transaction = sessionA.BeginTransaction())
{
    var water = sessionA.Get<BodyOfWater>(1);
    water.Freeze();
    sessionA.Update(water);

    // Same point in time as the line indicated below...

    transaction.Commit();
}

User B tries to boil the water (now ice!)...

using (var transaction = sessionB.BeginTransaction())
{
    var water = sessionB.Get<BodyOfWater>(1);

    // ... Same point in time as the line indicated above.

    water.Boil();
    sessionB.Update(water);
    transaction.Commit();
}

... and is successful!!! What? User A froze the water. Shouldn't an exception have been thrown saying "You cannot boil a Solid!"? User B fetched the data before User A had saved his changes, so to both users, the water appeared to initially be a liquid, so both users were allowed to save their conflicting state changes.

Solution

To prevent this scenario, we can add a Version property to the class and map it in NHibernate with a <version /> mapping:

public virtual int Version { get; set; }

This is simply a number that NHibernate will increment every time it updates the record, and it will check to make sure no-one else has incremented the version while we weren't watching. Instead of a concurrency-naive sql update like...

update BodyOfWater set State = 'Gas' where Id = 1;

... NHibernate will now use a smarter query like this:

update BodyOfWater set State = 'Gas', Version = 2 where Id = 1 and Version = 1;

If the number of rows affected by the query is 0, then NHibernate knows something went wrong - either someone else updated the row so that the version number is now incorrect, or someone deleted the row so that that Id no longer exists. NHibernate will then throw a StaleObjectStateException.

Special Note about Web Apps

The more time there is between the initial select of the data and the subsequent update, the greater the chance for this type of concurrency problem. Consider a typical "edit" form in a web app. The existing data for an entity is selected from the database, placed into the HTML form, and sent to the browser. The user may spend several minutes modifying the values in the form before sending it back to the server. There may be a decent chance that someone else was editing the same information at the same time, and they saved their changes before we did.

Making sure the version doesn't change during the few milliseconds we're actually saving the changes might not be enough in a scenario like this. To address this problem, you could send the version number to the browser as a hidden field along with the rest of the form fields, then check to make sure the version hasn't changed when you fetch the entity back out of the database before saving. In addition, you can limit the amount of time in-between the initial select and the final update by providing separate "view" and "edit" views instead of just using an "edit" view for everything. The less time the user spends on an "edit" view, the less chance that they'll be presented with an annoying error message saying that their changes could not be saved.