Transactions are often used within stored procedures to guarantee that all steps succeed or all steps fail. For Example, if...
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.
CREATE PROCEDURE [dbo].[uspInsertProduct] @Name VARCHAR(255), @ProductNumber VARCHAR(255), @ListPrice numeric(9,2) AS BEGIN DECLARE @returnCode INT BEGIN TRY EXEC @returnCode = sp_getapplock -- name of the resource. unique name nvarchar(255), truncated to 255 if longer @Resource = 'uspInsertProduct', --lock_mode is nvarchar(32) and has no default value. -- Shared, Update, IntentShared, IntentExclusive, or Exclusive. @LockMode = 'Exclusive', --Is the owner of the lock, which is the lock_owner value when the lock was requested. lock_owner is nvarchar(32). --The value can be Transaction (the default) or Session. --When the lock_owner value is Transaction, by default or specified explicitly, sp_getapplock must be executed from within a transaction. @LockOwner = 'Session', --Is a lock time-out value in milliseconds. The default value is the same as the value returned by @@LOCK_TIMEOUT. --To indicate that a lock request should return an error instead of wait for the lock when the request cannot be granted immediately, specify 0. @LockTimeout = 50, --Is the user, role, or application role that has permissions to an object in a database. --The caller of the function must be a member of database_principal, dbo, or the db_owner fixed database role to call the function successfully. --The default is public @DbPrincipal = 'public' IF @returnCode NOT IN (0, 1) BEGIN RAISERROR ( 'Unable to acquire exclusive Lock on uspInsertProduct', 16, 1 ) RETURN END --SLEEP FOR 10 SECONDS WAITFOR DELAY '00:00:10'; --insert product INSERT INTO tblProduct([Name], [ProductNumber], [ListPrice]) VALUES (@Name, @ProductNumber, @ListPrice) --release the lock on stored proc EXEC @returnCode = sp_releaseapplock @Resource = 'uspInsertProduct', @LockOwner = 'Session', @DbPrincipal = 'public' END TRY BEGIN CATCH IF @returnCode IN (0, 1) BEGIN EXEC @returnCode = sp_releaseapplock @Resource = 'uspInsertProduct', @LockOwner = 'Session', @DbPrincipal = 'public' END DECLARE @ErrMsg VARCHAR(4000) SELECT @ErrMsg = ERROR_MESSAGE() RAISERROR(@ErrMsg, 15, 50) END CATCH END
I have created a simple table tblProduct in AdventureWorks database for my example.
CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](255) NOT NULL, [ProductNumber] [varchar](255) NOT NULL, [ListPrice] [numeric](9, 2) NOT NULL, CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
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.