SET LOCK_TIMEOUT is ignored???

Question

SET LOCK_TIMEOUT is ignored???

digitalox

SSCommitted

Points: 1830
More actions
June 26, 2014 at 10:26 am

#308496

I have set LOCK_TIMEOUT during my index rebuild but it doesn't work. the alter index gets blocked indefinitely creating a chain of blocking. Am I doing something wrong or is there another workaround for this?
Thank You
Scott

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

How you are trying to do it? I believe you are doing in the script where you have your index rebuild specified. In that case it should work.

I just tested it and it is working.

In one window inside a transaction I have the below code.

begin tran

update users set ExternalId = 2

where userid = 1

In another window I have the below code.

set lock_timeout 1000

alter index PK_Users on users rebuild

and if I have open transaction it give me the error

Msg 1222, Level 16, State 56, Line 2

Lock request time out period exceeded.

digitalox SSCommitted Points: 1830 More actions · Answer 2

I was iterating the indexes of the database and executing the alter index with dynamic sql. It appears the set statement does not take affect unless you actually execute as part of the dynamic sql. I thought it was in effect for the whole session from the time of execution but i guess not.

Thanks!

Scott

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 3

Like this?

set lock_timeout 1000

declare @SQL nvarchar(100)

set @SQL = 'alter index PK_Users on users rebuild '

exec sp_executesql @SQL

It is still working for me.

digitalox SSCommitted Points: 1830 More actions · Answer 4

Nevermind, I found it. The lock timeout was being reset by a nested function that doing the actual execute sql. Argh.

Thanks again.

--

Scott