SQL2008: Lock a Stored Procedure for Single Use Only
Create a Custom Error Message, Designating an User assigned Error Number, starting at 50000, or higher, and a user designated Error Message.
USE Northwind GO EXECUTE sp_addmessage @msgnum = 51001, @severity = 16, @msgtext = N'Resource NOT Available', @lang = 'us_english', @replace = REPLACE
Create a Stored Procedure that Can be 'locked'
CREATE PROCEDURE dbo.Employees_U_LastName ( @EmployeeID int, @LastName varchar(20) ) AS BEGIN SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION DECLARE @LockResult int EXECUTE @LockResult = sp_getapplock @Resource = 'RepeatableRead_TRANSACTION', @LockMode = 'Exclusive', @LockTimeout = 0 IF @LockResult <> 0 BEGIN ROLLBACK TRANSACTION RAISERROR ( 51001, 16, 1 ) RETURN END -- All code between the use of sp_getapplock above, -- and sp_releaseapplock below will be restricted to -- only one user at a time. -- Ten Second delay for Demonstration Purposes WAITFOR DELAY '00:00:10' -- Remove these three lines for 'Normal' use UPDATE Employees SET LastName = @LastName WHERE EmployeeID = @EmployeeID EXECUTE sp_releaseapplock @Resource = 'RepeatableRead_TRANSACTION' COMMIT TRANSACTION END Demonstration
Open two separate Query Windows, and place the following code in one of the windows.
DO NOT Execute the code until both Query Windows are in place and connected to the database.
Query Window 1
EXECUTE dbo.Employees_U_LastName @EmployeeID = 1, @LastName = 'Davolio-Jones' SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = 1
Place the following code in Query window 2
Query Window 2
EXECUTE dbo.Employees_U_LastName @EmployeeID = 1, @LastName = 'Davolio' SELECT EmployeeID, LastName, FirstName FROM Employees WHERE EmployeeID = 1
Execute the code in Query window 1, and then immediately execute the code in Query window 2.
Results in Query Window 2
Msg 51001, Level 16, State 1, Procedure Employees_U_LastName, Line 21 Resource NOT Available
As you can see, it becomes possible to control code to single use only.
Clean up the test environment
DROP PROCEDURE dbo.Employees_U_LastName
Concurrency and Performance Considerations
Applications should capture the error, and handle appropriately, perhaps attempting to EXECUTE the query after a brief delay.
