This is another post in a series of posts on creating performant and scalable web APIs using ASP.NET core 2.0. In this post we’ll have a little look at isolation levels in SQL Server which is something that is often not thought about … until we hit performance issues …

By default, SQL Server uses the Read Committed isolation level and by default this blocks reads whilst updates take place. This can lead to scalability issues if we have lots of users reading data in addition to users writing data - particularly when the writes are heavy.

Let’s look at a simple example of simulating some users updating a contact record whilst other users get contact records.

We’ll start of with getting a benchmark for users just getting contact records. Here’s our Dapper data access code to get a contact record:

public Contact GetContactById(Guid contactId)
{
    Contact contact = null;

    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        string sql = @"SELECT ContactId, Title, FirstName, Surname,
                AddressType.Type AS AddressType, Address.AddressLine1, Address.AddressLine2, Address.AddressLine3, Address.Town, Address.County, Address.Country, Address.Postcode,
                EmailAddressType.Type AS EmailAddressType, EmailAddress.Email AS EmailAddress,
                PhoneNumberType.Type As PhoneNumberType, PhoneNumber.Number AS PhoneNumber,
                Contact.RowVersion
            FROM Contact
                LEFT JOIN Address ON Contact.ContactPK = Address.ContactPK
                    LEFT JOIN AddressType ON Address.AddressTypePK = AddressType.AddressTypePK
                LEFT JOIN EmailAddress ON Contact.ContactPK = EmailAddress.ContactPK
                    LEFT JOIN EmailAddressType ON EmailAddress.EmailAddressTypePK = EmailAddressType.EmailAddressTypePK
                LEFT JOIN PhoneNumber ON Contact.ContactPK = PhoneNumber.ContactPK
                    LEFT JOIN PhoneNumberType ON PhoneNumber.PhoneNumberTypePK = PhoneNumberType.PhoneNumberTypePK
            WHERE Contact.ContactId = @ContactId
                AND Address.IsPrimary = 1 AND EmailAddress.IsPrimary = 1 AND PhoneNumber.IsPrimary = 1";

        contact = connection.QueryFirstOrDefault<Contact>(sql, new { ContactId = contactId });
    }
    return contact;
}

Here’s a snipet of results from our load test on the API endpoint:

On to our data access code for updating a contact record … We’ve simulated a long database write by using a Thread.Sleep resulting in the updating taking > 1 sec.

public void UpdateContactName(Contact contact)
{
    using (IDbConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (IDbTransaction transaction = connection.BeginTransaction())
        {
            connection.Execute(@"UPDATE Contact
                        SET Title = @Title, FirstName = @FirstName, Surname = @Surname
                        FROM Contact
                        WHERE ContactId = @ContactId", contact, transaction);

            System.Threading.Thread.Sleep(1000);

            transaction.Commit();
        }
    }
}

Ok, lets load test some users updating a contact record whilst other users are getting the contact record:

We see that the GET requests take far longer than they were previously because they are being blocked by the PUT requests.

So, what can we do? How can we resolve this? It is tempting to use the Read Uncommitted isolation level. This appears to resolve the problem …

public void UpdateContactName(Contact contact)
{
    using (IDbConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();

        using (IDbTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted))        {
            connection.Execute(@"UPDATE Contact
                        SET Title = @Title, FirstName = @FirstName, Surname = @Surname
                        FROM Contact
                        WHERE ContactId = @ContactId", contact, transaction);

            System.Threading.Thread.Sleep(1000);

            transaction.Commit();
        }
    }
}

… but what if the SQL did more database writes after the UPDATE on the Contact table and in the process errored and rolled back? We’d be potentially reading and returning incorrect data!

There is a much simpler solution … We can change the Read Committed isolation level behaviour to not block and instead read the current committed data. We change this on the database using the following SQL command:

ALTER DATABASE <Our database name>
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
GO

So, no code changes - sweet!

If we now do the same load test again, the GET requests are much faster because they are not being blocked by the the PUT requests:

In summary, having READ_COMMITTED_SNAPSHOT ON can generally lead to better performance without losing any accuracy in the data.

Comments

Anderson February 15, 2018

Hey,

Nice series of posts! When I started reading I thought you were going to use the IsolationLevel.ReadUncommitted with the query, but you used it with the update. Is that right?

Maybe if your update statement was used with a select we could see some read uncommitted effect.

Carl February 16, 2018

Good question Anderson, yes, you would get the same result if you put a IsolationLevel.ReadUncommitted transaction around the GET query.

David February 18, 2018

Excellent! Brief and to the point, and best of all no actual code changes! Win-win-win!