Scalable and Performant ASP.NET Core Web APIs: SQL Server Isolation Level

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:

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

GET benchmark

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.

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

GET requests blocked

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 ….

… 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:

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:
GET requests faster

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

Don’t forget to subscribe to the rest of this series in the footer of this page!

Share this:Share on RedditTweet about this on TwitterShare on LinkedInShare on Google+

3 Comments

  1. 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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *