Preventing race conditions with sp_getapplock

This article covers sp getapplock / releaselock storage procedures and its use in a simple way including simple functions that can be used with Entity Framework and C#.

• Preventing race conditions with sp_getapplock resource
• Stored Procedure Example Using sp_getapplock resource
• Using sp_getapplock to lock a resource
• Understanding sp_releaseapplock and how to release the lock owner
• Using sp_releasapplock to release a resource
• SQL Server sp_getapplock
• Frequently Asked Questions

Preventing race conditions with sp_getapplock resource

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.

Stored Procedure Example Using sp_getapplock resource

Now you should see sp_getAPPlock used in storage. The sample procedure critical section_worker is an automated transaction controller, message messaging, and error handle, reminiscent of a real life process that utilizes sp_getapplock.

Here are scripts for creating them as permanent processes in tempDB. During transactions the lock can remain in place and can be released either through COMMIT at the close of the tries or through ROLLBACK in the catch blocks. It is possible that it uses RAIERROR. NOW AIT rather than print a file.

It’s not an error and adding the WITHNOWAIT will force reloading the message and any prior messages as quickly as possible.

Setting up sp_getapplock resource

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.

Using sp_getapplock to lock a resource

Sp_getapplock is called for an sp_getapplock. This sp_getapplock calls the resource “MyLock” for a lock and holds it until the transaction is active. In that case it waits about 10 seconds before executing the command “COMMIT”.

The process works by executing the code below, the code is now executed immediately. Open another Management Studio window. The first session began and then returned the result immediately. Immediately after the second session is completed you can see how quickly the application is performing the query.

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.

Understanding sp_releaseapplock and how to release the lock owner

Sp_releasingapplock uses an app-released method to lock resources. Below is the syntax for sp_ releaseapplock. The argument @resourcename =’resource_name’ contains a name for the resources that are requested. ResourceName datatype is nvarchar.

When resourcename is more than 255 characters the value is cut in half. In the case of application resourcenames are unique. Resource names are binary comparisons therefore it’s case sensitive.

Releasing the lock with sp_releaseapplock resource

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

Using sp_releasapplock to release a resource

This call is required by @Resource and @LockOwner but looks like this when @LockOwner=’Transaction’. Then sp_releaseapp lock is performed during that transaction.

Moreover, when the locking is not held by the transaction SQL Server does not return a return code, the code will throw the explicit error which must be considered. If you want this reason, I call SPI_ReleverAPPlock instead and instead use COMMIT.

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.

SQL Server sp_getapplock

In a stored procedure the method of sp-getapplock locks the resource. Below are syntaxes from sp.getapplock: EXEC Sp. GetApplock @Resource =’resource_name,@lockmode =’lockmode’. Lockowners = lockowner.

Arguments

The sp-getapplock uses 5 arguments: @ResourceName = The Resource name of the resource where the lock should be placed. Resource names can be classified into two different type data types.

Whenever a resource name is more than 256 characters, the number of digits in the file are automatically truncated. Application names must be unique. The Resource Name’resource’ is a binary comparator hence the case is sensitive.

The @LockMode = ‘lock’ mode is the mode the lock will set on the resource. Lock mode’s data types are varchar32 and they do not have any default values. The Lock mode can be either one of the below: The @lockOwner =’lock_owner’ arguments are the owner of the Lock.

Return value

Returns values for the sp_getapp lock functions that are >0 (resilience).

Frequently Asked Questions

What is Sp_getapplock?

The locks resource created in sp_getapplock can now be found in the database of a session. Each key resource has a number of different values.

The main database name as defined with @DBPrincipal Parameters.

How do I lock a table in SQL Server?

Syntax for MySQL load-tables. Table_name: List of table on which you are applying LOCK. Lock Mode: The type of lock that will be placed on the table. It can be a choice in the “access sharing”.

Share updates, share exclusive, access exclusive, row exclusive share row exclusive.

How do I lock a stored procedure in SQL?

A better solution can be found in SQL Server. The system store procedures include the pairs sp – getapplock and sp.releaseapplock. It is also a way in SQL that uses the underlying security of a database without requiring requiring rows in it.

How do you stop a table from locking in SQL Server?

Use of the following method is possible in the reduction of locking contention as well as the increase in overall throughput.

Do not allow for situations where many systems try to update or insert data to one page. Refrain from transactions with interaction. Make changes to data as short as possible.

What is Xp_userlock in SQL Server?

SP_getaplock provides wrappers for the extended procedure XPUSERLOCK. The lock mechanism allows for managing concurrency outside tables and columns by using SQL SERVER.

Does stored procedure lock table?

A stored procedure does not obtain table locking. The entire code executed within a stored process has been stored as a log in the Binary Log even in statement binary recording.