ROWLOCK Escalation

Question

Post reply

ROWLOCK Escalation

chrisfradenburg

SSCrazy Eights

Points: 9592
More actions
March 17, 2012 at 7:52 pm

#252957

Comments posted to this topic are about the item ROWLOCK Escalation
March 19, 2012 at 2:46 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 25 total)

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

baabhu SSCertifiable Points: 6205 More actions · Answer 1

I completely miss read the question. And also I never heard of the lock escalation threshold. Good to knew it.

Learned something knew.

Good question !!!!.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 2

Learned something new, but the reference could be more complete.

The MSDN article never states that the ROWLOCK hint can/will be ignored after the threshold has been passed.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.

What the explanation also doesn't say is that escalations are always¹ to table. SQL never escalates to a page lock, when escalation is triggered it is to table.

(1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andagr SSC-Addicted Points: 465 More actions · Answer 4

GilaMonster (3/19/2012)
The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.
What the explanation also doesn't say is that escalations are always¹ to table. SQL never escalates to a page lock, when escalation is triggered it is to table.
(1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

I agree that the explanation for this question is a bit vague, it says:

"SQL will start out with rowlock, but once it reaches the lock escalation threshold, the lock will still escalate to a courser lock."

This would indicate that page is a correct answer, but it is not. MSDN states:

"The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks."

/@devandreas

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

This is one of those questions that I answered randomly because I wanted to see what the answer was...would have been nice to get a slightly more in-depth explanation. Still, Gail came through on that for me, which is what I like about this site--if it's not answered by the question author somebody in the comments will do it! 🙂

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

GilaMonster (3/19/2012)
The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.
What the explanation also doesn't say is that escalations are always¹ to table. SQL never escalates to a page lock, when escalation is triggered it is to table.
(1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

Thanks for the explanation.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Skanker Hall of Fame Points: 3059 More actions · Answer 7

Thanks for the explanations. Learned something today, which is always a good start to the day.

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 8

GilaMonster (3/19/2012)
The RowLock (or pageLock) hints just say what the locks should start as, nothing more. In this case they'll start as row, but once past the 5000 or so locks acquired, will escalate.
What the explanation also doesn't say is that escalations are always¹ to table. SQL never escalates to a page lock, when escalation is triggered it is to table.
(1) The one exception is 2008, with a partitioned table where the table's escalation setting has been changed to AUTO. In that case (and only that case), escalation will be to the partition only, not the table.

Thanks.

Keld Laursen (SEGES) SSC Eights! Points: 843 More actions · Answer 9

Missed one here :angry:

I knew that locks would escalate, so I checked what the rowlock hint would do.

This article hinted (to me at least) that the escalation would be put off until later on servers with more memory.

If not, then I didn't really see the point of the rowlock hint as I would have assumed rowlock would be first always. (Other hinting aside, of course)

Bummer

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

Got it right because I remembered that lock escalations are always made directly to the table level. Thank you for the question! Very interesting subject.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Nice easy question to start the week. I knew roughly what goes on so got it right, but the comments (thanks, Gail) have made it clearer.

Tom

jfogel SSCarpal Tunnel Points: 4155 More actions · Answer 12

Thanks Gila' for the explanation. I'm one of the dummies who thought it was a page lock.

Cheers

richardd Hall of Fame Points: 3963 More actions · Answer 13

Slight nit-picking issue: what is a "courser" lock? Do you mean a coarser lock?

http://dictionary.reference.com/browse/Courser

http://dictionary.reference.com/browse/Coarser