Execute Stored Procedure with SET LOCK_TIMEOUT

Question

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).

• SqlWorldWide: any way how to call it explicitly--You mean at a session level?
• DNac: Yes. Pseudocode: EXEC dbo.LoadData WITH SET LOCK_TIMEOUT 10.
• Joe Obbish: Do yo mind explaining why you don't want to set it in the stored procedure itself?
• DNac: Sure. There is one Master Stored procedure that starts other stored procedure based on some logic. So I want to move the LOCK TIMEOUT logic to this master stored proc and handle all the logic there.
• James: It might be better to batch your inserts, then dial down the rows inserted until it finishes under your deadline.

Answer 1

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 ;-).

Answer 2

No you cannot do that. This is a property of your connection.

Books online:

At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection. The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

Few example and discussion of how it can be used:

1. SET LOCK_TIMEOUT, is it session or statement based?
2. https://blog.sqlauthority.com/2013/01/28/sql-server-basic-explanation-of-set-lock_timeout-how-to-not-wait-on-locked-query/
3. Can we set LOCK_TIMEOUT instance- or database-wide?