LOCK_TIMEOUT

Steven · October 14th, 2008, 05:14 PM

I'm having a bit of trouble with locking:
Say I have 2 stored procedures, P1 and P2.
P1 calls P2, and I want the whole thing to abort after a certain length of time, but I'm not having any luck.
Here's the basic thing that I have:

Code:

CREATE PROCEDURE P1
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 2000
BEGIN TRAN
 --Do some stuff
COMMIT TRAN

BEGIN TRAN
  EXEC P2
  -- Do some more stuff
COMMIT TRAN
GO

What I want to happen is for the first transaction in P1 to run, which will take less than 2 seconds, then run P2 in the second transaction. P2 is a really long running SP, and I want it to stop if it doesn't get the whole way through.
However, despite setting LOCK_TIMEOUT, it still just keeps on running when I issue:

Code:

EXEC P1

in query analyser, taking over 3 minutes.

Anyone know how I can fix this?

I am a loud man with a very large hat. This means I am in charge

Jeff Moden · October 14th, 2008, 09:03 PM

Lock Timeout doesn't control duration of run... it quite literal checks how long it takes to get a lock on a table.

You can try SET QUERY_GOVERNOR_COST_LIMIT n with the understanding that it has nothing to do with duration. Only QUERY TIMEOUT is done by duration and THAT's only for "remote" queries. You could make a linked server to itself to try to make the local query a "remote" query. I don't know of anything else that will limit the duration of a query.

--Jeff Moden