Helping you solve those tough coding problems!

Preventing race conditions with sp_getapplock Preventing race conditions with sp_getapplock

Published on Apr 17, 2019 by Jamie Munro

I am a big proponent of storing calculated fields in the database opposed to calculating them each time they are displayed. They only change at specific times so you just need to cover those spots to calculate them. The danger with storing calculated fields is that concurrent calls have a chance incorrectly calculating the field. Languages such as C# offer the ability to lock objects to help with this. However, in a multi-server example, locking on a single server will not work. In this article I'm going to demonstrate how to use sp_getapplock to prevent race conditions.




An example of a calculated field is if you have an album of pictures, the count of pictures can be stored as a calculated field. It will only change when you add or delete a picture. To prevent a race condition we want to ensure only one picture is being added or deleted simultaneously. Enter the ability to lock at the database level using sp_getapplock.

My approach to preventing concurrent requests is as follows:


  1. At the start of the action acquire a lock.

  2. The lock is applied to a specific resource. In our example it can be the ID of the album.

  3. To make an error message more generic I'll provide some additional data to build a dynamic error message if the lock cannot be acquired.

  4. Any concurrent requests will also attempt to acquire a lock, if it fails an exception will be thrown preventing the action.

  5. When the action is completed and the calculated field has been updated, release the lock to allow future requests to process.




Setting up sp_getapplock



Below is a function that I have created called AcquireLock. It accepts two parameters: the name of the resource to lock and the action being performed, e.g. add or delete.


        public void AcquireLock(string resourceName, string action)
        {
            using (var cmd = Context.Database.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_getapplock";
                cmd.Parameters.Add(new SqlParameter("Resource", resourceName));
                cmd.Parameters.Add(new SqlParameter("LockMode", "Exclusive"));
                cmd.Parameters.Add(new SqlParameter("LockOwner", "Session"));
                cmd.CommandTimeout = 5;

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw new ConflictException(
                        string.Format(
                            "Unable to perform {0}.  Too many concurrent processes are attempting to access {1}", action,
                            resourceName));
                }
            }
        }


In my code, I'm using Entity Framework with a database context called Context. I use this object to access my database. If you are not using EF then you would open your database connection as normal.

In this function I create a SqlCommand that executes a stored procedure. In this case the stored procedure is called sp_getapplock. This stored procedure takes three parameters:


  1. The name of the resource to lock. E.g. album_1

  2. The LockMode which I want an Exclusive lock.

  3. The LockOwner which I want to last the length of the session.



Here is the important part. I've set the SqlCommand to timeout after 5 seconds. This means that if a second request occurs while the first request is still running, the command will wait 5 seconds for the lock to release. If the lock is not released within 5 seconds the command will error. If the lock is released before the 5 seconds, the second request will simply wait and start processing once the first request completes. This is what prevents the race conditions.

If the command times out before the first request did not complete I catch the Exception and throw a ConflictException. This allows me to catch a ConflictException at a higher level and return a 429 Conflict Status Code with a friendly error about not performing concurrent requests.

Releasing the lock with sp_releaseapplock



I've also created a second function that releases the sp_getapplock when the request is finished. This function is called ReleaseLock. It accepts a single parameter which is the name of the resource that you previously defined the lock on.


        private void ReleaseLock(string resourceName)
        {
            using (var cmd = Context.Database.Connection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_releaseapplock";
                cmd.Parameters.Add(new SqlParameter("Resource", resourceName));
                cmd.Parameters.Add(new SqlParameter("LockOwner", "Session"));
                cmd.CommandTimeout = 5;

                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }
            }
        }


This function performs the same thing but instead it calls the stored procedure sp_releaseapplock.

Here is an example of how to use these functions:


public function UploadPhoto(long id) {
   AcquireLock("album_" + id, "upload");

   // Perform work to upload photo

   // Update the photo count for the album

   // Save the changes to the database

   ReleaseLock("album_" + id);
}


That's it, no more race conditions with concurrent requests.

Tags: SQL | Entity Framework | ASP.NET | mssql | sp_getapplock

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP