Application lock

Application locks open up the whole world of SQL Server locks for custom uses within applications. Instead of using data as a locked resource, application locks use any named user resource declared in the sp_GetAppLock stored procedure.

1. APPLOCK_MODE

As an application lock function, APPLOCK_MODE operates on the current database. The database is the scope of the application locks. Syntax:

APPLOCK_MODE( 'database_principal' , 'resource_name' , 'lock_owner' )

Returns the lock mode held by the lock owner on a particular application resource. Lock mode can have any one of these values:

NoLock, Update, *SharedIntentExclusive, IntentShared, IntentExclusive, *UpdateIntentExclusive, SharedExclusive

*This lock mode is a combination of other lock modes and sp_getapplock cannot explicitly acquire it.

Function properties: Nondeterministic, Nonindexable, Nonparallelizable.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/functions/applock-mode-transact-sql?view=sql-server-ver15

2. sp_getapplock and sp_releaseapplock

The stored procedure sp_getapplock places a lock on an application resource. The stored procedure sp_releaseapplock releases a lock on an application resource.

Here is a good article:

https://www.sqlshack.com/an-overview-of-sp_getapplock-and-sp_releaseapplock-stored-procedures

Application locks must be obtained within a transaction. As with the locks the engine puts on the database resources, you can specify the lock mode (Shared, Update, Exclusive, IntentExclusive, or IntentShared).

The return code indicates whether the procedure was successful in obtaining the lock, as follows: 0: Lock was obtained normally. 1: Lock was obtained after another procedure released it. -1: Lock request failed (timeout). -2: Lock request failed (canceled). -3: Lock request failed (deadlock). -999: Lock request failed (other error).

(https://www.oreilly.com/library/view/microsoft-sql-server/9781118282175/c47_level1_8.xhtml)

In conclusion, a resource defined by your application can be locked by using sp_getapplock so that you can lock any resource you want with a specified lock mode.

---

Example:

Answer:

#lock