This post has already been read 176 times!
DOTNET exposes several ways to achieve mutual exclusion in code such as Mutex, Lock, SynLock, Manual reset event or Thread wait etc. The same thing can be achieved even in SQL Server using application locks. It is achieved by using 2 system stored procs (sp_getapplock and sp_releaseapplock)
However to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions:
is in the db_owner role
is the DB Principal ID (e.g. guest)
is in the DB Principal ID role (e.g. public)
sp_getapplock ReturnCode Values:
|0||The lock was successfully granted synchronously.|
|1||The lock was granted successfully after waiting for other incompatible locks to be released.|
|-1||The lock request timed out|
|-2||The lock request was canceled.|
|-3||The lock request was chosen as a deadlock victim.|
|-999||Indicates a parameter validation or other call error.|
uspInsertProduct (Stored Proc)
I have created a stored procedure [uspInsertProduct] which would insert a new product into tblProduct table. In this storedproc, we use sp_getapplock to lock the storedproc until we are done inserting the product. Once processing is done, we explicitly release the lock by calling sp_releaseapplock.
For our testing, I have added a wait statement that would block the execution of stored procedure, or transaction until a specified time is reached.
If an application lock owner is a transaction, the lock gets automatically released when the transaction ends. If the application lock owner is a session, connection has to be closed or sp_releaseapplock has to be called to release the resource.
I have created a simple table tblProduct in AdventureWorks database for my example.
Step 1: Open SQL Server Management Studio
Step 2: Open 2 SQL Queries windows
Step 3: In one Window, copy below statement
EXEC [uspInsertProduct] ‘test product 1’, ‘test product number 1’, 5000
Step 4: In second Window, copy below statement
EXEC [uspInsertProduct] ‘test product 2’, ‘test product number 2’, 5000
Step 5: Go to First window press F5, immediately go to second window and press F5.
Step 6: First Window Output
It will successful get the lock and inserts the record. But sleeps for 10 seconds before it releases the lock.