Use sp_getapplock, sp_releaseapplock
EXECUTE sp_addmessage
@msgnum = 51001,
@severity = 16,
@msgtext = N'Resource NOT Available',
@lang = 'us_english',
@replace = REPLACE
Return to Top
CREATE PROCEDURE dbo.Employees_U_LastName
( @EmployeeID int,
@LastName varchar(20)
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
DECLARE @LockResult int
--sphelp sp_getapplock
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
