Preventing race conditions with sp_getapplock

sp_getapplock

sp_getapplock



At the start of the action acquire a lock.

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

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.

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

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

AcquireLock

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)); } } }

Context

SqlCommand

sp_getapplock



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

The LockMode which I want an Exclusive lock.

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



SqlCommand

Exception

ConflictException

ConflictException

Releasing the lock with sp_releaseapplock

sp_getapplock

ReleaseLock

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) { } } }

sp_releaseapplock

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); }

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 useto 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 usingMy approach to preventing concurrent requests is as follows:Below is a function that I have created called. It accepts two parameters: the name of the resource to lock and the action being performed, e.g. add or delete.In my code, I'm using Entity Framework with a database context called. 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 athat executes a stored procedure. In this case the stored procedure is called. This stored procedure takes three parameters:Here is the important part. I've set theto 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 theand throw a. This allows me to catch aat a higher level and return a 429 Conflict Status Code with a friendly error about not performing concurrent requests.I've also created a second function that releases thewhen the request is finished. This function is called. It accepts a single parameter which is the name of the resource that you previously defined the lock on.This function performs the same thing but instead it calls the stored procedureHere is an example of how to use these functions:That's it, no more race conditions with concurrent requests.