Hello,
I have found the solution of the issue which I was facing.
By Adding the sp_getapplock statement at first statement of sp and sp_releaseapplock at last statement of sp it resolve my issue.
I've added it like below :
CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'TestSPLock', @LockMode = 'Exclusive';
//Logic of SP
EXEC sp_releaseapplock @Resource = 'TestSPLock';
COMMIT TRANSACTION;
END
Thanks,