This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
In certain circumstances, we need to apply production changes or exclusive access to one or more resources by only one instance of our application, blocking other application-level accesses. With this example, using the sp_getapplock and sp_releaseapplock functions allows us to perform these functions. Additionally, we can also use the applock_test function that allows us to know whether it is possible to block the content or not depending on our needs.
In the following code,
using System;
using System.Data;
using Microsoft.Data.SqlClient;
using System.Threading;
namespace GetAppLock
{
class Program
{
private static string connectionString = "Server=tcp:servername.database.windows.net,1433;User Id=username;Password=MyPwd;Initial Catalog=MyDb;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Pooling=true;Max Pool size=100;Min Pool Size=1;ConnectRetryCount=3;ConnectRetryInterval=10;Application Name=ConnTest";
private static string resourceName = "Example";
static void Main(string[] args)
{
bool checkResourceStatus = false; // Variable to determine whether to check resource status
if (checkResourceStatus)
{
// Check if the resource is locked
int lockMode = GetAppLockMode(connectionString, resourceName);
Console.WriteLine($"Resource '{resourceName}' status: {lockMode}");
}
else
{
// Attempt to acquire the lock with retry policy and unlock
AttemptToAcquireLock(connectionString, resourceName);
}
}
static int GetAppLockMode(string connectionString, string resourceName)
{
int lockMode = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlCommand command = new SqlCommand("SELECT APPLOCK_TEST(@DBOwner, @Resource,@LockMode,@LockOwner)", connection, transaction))
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@DBOwner", "dbo");
command.Parameters.AddWithValue("@Resource", resourceName);
command.Parameters.AddWithValue("@LockMode", "Exclusive"); // Exclusive mode
command.Parameters.AddWithValue("@LockOwner", "Session");
lockMode = (int)command.ExecuteScalar();
transaction.Commit();
}
}
}
return lockMode;
}
static void AttemptToAcquireLock(string connectionString, string resourceName)
{
int maxRetries = 5; // Maximum number of retries
int retryDelaySeconds = 15; // Delay between retries (in seconds)
for (int retryCount = 1; retryCount <= maxRetries; retryCount++)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
connection.Open();
// Attempt to acquire the lock
using (SqlCommand command = new SqlCommand("sp_getapplock", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Resource", resourceName);
command.Parameters.AddWithValue("@LockMode", "Exclusive"); // Exclusive mode
command.Parameters.AddWithValue("@LockOwner", "Session"); // Transaction owner
command.Parameters.AddWithValue("@LockTimeout", 50);
command.Parameters.AddWithValue("@DBPrincipal", "dbo");
SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int result = (int)returnValue.Value;
if (result >= 0)
{
Console.WriteLine("Lock granted successfully.");
// Perform your exclusive operations here
ReleaseAppLock(connection, resourceName);
break; // Exit the loop if the lock is obtained
}
else
{
Console.WriteLine($"Attempt {retryCount}: Failed to acquire the lock.");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error in attempt {retryCount}: {ex.Message}");
}
}
if (retryCount < maxRetries)
{
Console.WriteLine($"Waiting {retryDelaySeconds} seconds before the next attempt...");
Thread.Sleep(retryDelaySeconds * 1000); // Convert seconds to milliseconds
}
else
{
Console.WriteLine("Maximum retry attempts reached. Aborting.");
}
}
}
static void ReleaseAppLock(SqlConnection connection, string resourceName)
{
using (SqlCommand command = new SqlCommand("sp_releaseapplock", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Resource", resourceName);
command.Parameters.AddWithValue("@LockOwner", "Session");
command.Parameters.AddWithValue("@DBPrincipal", "dbo");
SqlParameter returnValue = new SqlParameter("@ReturnValue", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
command.ExecuteNonQuery();
int result = (int)returnValue.Value;
if (result >= 0)
{
Console.WriteLine("Lock remove successfully.");
}
else
{
Console.WriteLine("Failed removing the lock");
}
}
}
}
}
Exclusive Access with sp_getapplock and sp_releaseapplock
SQL Server's sp_getapplock function is designed to grant application-level locks, providing a mechanism to serialize access to resources within the database.
This stored procedure allows applications to define arbitrary resource names and request locks on these resources, which are not tied to specific database objects like tables or rows but rather to logical operations or transactions within the application.
The lock can be requested in various modes, with "Exclusive" mode ensuring that only one instance of the application can access the resource at a time.
Once an exclusive lock is acquired using sp_getapplock, other instances of the application that attempt to acquire a lock on the same resource will either be blocked or fail, depending on the lock timeout specified.
This ensures that critical sections of the application, such as those performing updates or batch processing, are protected from concurrent access.
Using APPLOCK_TEST function
Before attempting to acquire a lock, you could use APPLOCK_TEST that can be obtained without causing undue delay or blocking if possible to grant the operation. The APPLOCK_TEST function serves this purpose by enabling applications to check the current lock status of a resource.
By providing the resource name, lock mode, and lock owner, applications can determine if acquiring a lock would be immediately successful, would require waiting, or would be outright impossible due to existing incompatible locks.
Practical Implications and Use Cases
The practical implications of using sp_getapplock, sp_releaseapplock, and APPLOCK_TEST are vast.
In scenarios where database maintenance, batch updates, or critical data migrations need to be performed, ensuring exclusive access can prevent data logical data integrity. Moreover, in multi-user environments where applications might attempt concurrent operations that should logically be serialized, these functions provide a layer of control and security that goes beyond traditional transactional locking mechanisms.
Conclusion
SQL Server's application-level locking functions offer a robust framework for managing exclusive access to critical operations within an application. By judiciously applying sp_getapplock, sp_releaseapplock, and APPLOCK_TEST, developers can safeguard their applications against concurrency issues, ensure data integrity during crucial updates, and maintain a high level of application stability and reliability in production environments.
Links
APPLOCK_TEST (Transact-SQL) - SQL Server | Microsoft Learn
sp_getapplock (Transact-SQL) - SQL Server | Microsoft Learn
sp_releaseapplock (Transact-SQL) - SQL Server | Microsoft Learn
Note
Please note that the C# code provided here is primarily shared for illustrative and educational purposes. Before implementing it into your project or work, it is essential that you check and thoroughly test it.
Ensure that the code meets your requirements and functions as expected within the context of your application. Remember, you are the ultimate responsible party for any code that you decide to use.