Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old October 14th, 2008, 05:14 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default LOCK_TIMEOUT

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
__________________
<hr noshade size=\"1\"><i><font color=\"blue\">I am a loud man with a very large hat. This means I am in charge</i></font id=\"blue\">
  #2 (permalink)  
Old October 14th, 2008, 09:03 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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






Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.