Sql-server – Execute Stored Procedure with SET LOCK_TIMEOUT

query-timeoutsql serversql-server-2016stored-procedurest-sql

Is there anyway to execute stored procedure with SET LOCK_TIMEOUT explicitly?

E.g. I have sproc called dbo.LoadData. It just takes data from table dbo.Abc and insert them in table dbo.Xyz.

Now I want to execute the sproc dbo.LoadData, but if the table dbo.Abc is locked for e.g. more than 10 seconds, stop the execution of the stored procedure.

I know the option SET LOCK_TIMEOUT can be implemented in the sproc dbo.LoadData itself, but I am wondering if there is any way how to call it explicitly (= not implementing the option in the stored procedure itself).

Best Answer

LOCK_TIMEOUT is not only a session-level setting, it also needs to be set in a separate batch. But, it cannot be set via a variable. So, this can be accomplished by using Dynamic SQL in the "master" Stored Procedure. This will allow for setting LOCK_TIMEOUT and then executing whatever other Stored Procedure that should run within this particular setting. It has to be a single execution of Dynamic SQL since the setting will revert back to the value of the top-most / outer-most process. For example:

Run this in one query tab in SSMS:

GO
CREATE PROCEDURE ##ShouldTimeout
AS
INSERT INTO ##LockedTable ([ID]) VALUES (1);
GO


GO
CREATE PROCEDURE ##TimeoutTest
(
    @SecondsUntilTimeout INT = 2
)
AS
SET NOCOUNT ON;

SET @SecondsUntilTimeout = ISNULL(@SecondsUntilTimeout, 2); -- enforce default

DECLARE @SQL NVARCHAR(MAX) = N'SET LOCK_TIMEOUT ';
SET @SQL += CONVERT(NVARCHAR(MAX), @SecondsUntilTimeout * 1000) + N';
';

SET @SQL += N'EXEC ##ShouldTimeout;'; -- use CASE / IF to decide what to exec

RAISERROR(@SQL, 10, 1) WITH NOWAIT; -- print Dynamic SQL in "Messages" tab

EXEC (@SQL);
GO

Run the following in another query tab in SSMS:

BEGIN TRAN;
CREATE TABLE ##LockedTable (ID INT);

-- ROLLBACK

Now go back to the first query tab (where you created the two temporary Stored Procedures) and run the following two tests:

EXEC ##TimeoutTest;
-- this will timeout after 2 seconds (the default)


EXEC ##TimeoutTest 5;
-- this will timeout after 5 seconds

Now go back to the second query tab and execute the ROLLBACK ;-).

Related Question