3

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

  • any way how to call it explicitly--You mean at a session level? – SqlWorldWide Jul 10 '17 at 12:20
  • Yes. Pseudocode: EXEC dbo.LoadData WITH SET LOCK_TIMEOUT 10. – DNac Jul 10 '17 at 12:22
  • Do yo mind explaining why you don't want to set it in the stored procedure itself? – Joe Obbish Jul 11 '17 at 23:27
  • 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. – DNac Jul 17 '17 at 7:03
  • It might be better to batch your inserts, then dial down the rows inserted until it finishes under your deadline. – James Dec 21 '17 at 13:53
3

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

1

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.