CODEWITHSUNDEEP
sql-serverdatabasescalability
MrTibs
MrTibs

Reputation: 161

would selecting on too many fields cause a table lock?

I have a SELECT statement in a stored procedure that under very heavy load results in a timeout: "Lock request time out period exceeded." - at least that's what .NET throws. It is a pretty simple query on a table (CODES) that has a primary key and a clustered index (on TYPE_CODE1). The only thing that looks out of the ordinary is that there are many fields being selected on (all the fields except Dclass are bit fields). Would this cause the lock on the table? Any other ideas?

TIA
T

select
@TYPE_CODE1 = TYPE_CODE1,
@ALTERNATE_CODE = ALTERNATE_CODE,
@BANNER = BANNER,
@CODE_1 = CODE_1,
@CODE_2 = CODE_2,
@CODE_3 = CODE_3,
@CODE_4 = CODE_4,
from CODES with (nolock)
where
Dclass = @Dclass
and Ret = @Ret
and Rem = @Rem
and Ope = @Ope
and Res = @Res
and Cer = @Cer
and Cdo = @Cdo
and Del = @Del
and Sig = @Sig
and Ads = @Ads
and Adr = @Adr
and Emi = @Emi
and In1 = @In1
and In2 = @In2
and Paa = @Paa
and Reg = @Reg
and Red = @Red
and Rer = @Rer
and Ree = @Ree
and Rei = @Rei
and Spe = @Spe
and Mer = @Mer
and Hol = @Hol
and Day = @Day
and Sca = @Sca
and Sis = @Sis
and Poa = @Poa
and Haz = @Haz
and Sun = @Sun
and Out = @Out
and IsActive = 1

Upvotes: 2

Views: 311

Answers (2)

mpfefferle
mpfefferle

Reputation: 72

SELECT statements that use the NOLOCK hint on any table they access never cause locks or wait for them. So it shouldn't be this statement that caused the error.

Upvotes: -1

Kris Gruttemeyer
Kris Gruttemeyer

Reputation: 872

Lock Request Timeout exceeded doesn't always directly correlate to a table lock. That error means that the query was waiting to obtain a lock on an object in SQL server but couldn't do it fast enough, so the query timed out.

Additionally, SQL uses a process called lock escalation where, if a query requires more than 5000 locks (page/row level locks), it will request a full table lock. If you are reaching this 5000 lock threshold and trying to take a table lock out, it could be getting stuck behind some other process that already has a lock on it.

I'd try running your application and then, at the same time in management studio, use a tool like sp_Whoisactive and find out what's blocking your application and causing it to timeout. Odds are some other process has a lock on the table you are trying to query.

Upvotes: 3

Related Questions