ROWLOCK Escalation

Question

ROWLOCK Escalation

Viewing 11 posts - 16 through 25 (of 25 total)

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

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 1

tks for the question and tks Gail for the additional feedback. - cheers

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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 Gail for the explanation.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

zymos SSCommitted Points: 1960 More actions · Answer 3

The question was great and I liked the explanation of the table lock mechanism in SQL Server.

Thanks

Ken SSCoach Points: 17191 More actions · Answer 4

Thanks for the question Chris. Learned something today!

Thanks too to Gail for the additional explanation.

DBA_Dom SSCrazy Points: 2902 More actions · Answer 5

Gail I liked your explaination as I have read about this several times. I am having to go through one of our databases to clean up performance left in the wake of non thoughtful developers. This table level lock is also more specifically because the table does not have a nonclustered index on what is being searched therefore needs to do a fulltable scan with intent to update which will lock the table regardless of escalation threshold and rowlock hint.

mtassin SSC-Insane Points: 23099 More actions · Answer 6

thanks for the explanation Gail....

I didn't this one for sure, but looking at 7500 rowlocks I figured it would escalate... and I kind of remembered that things don't go to page locks.... they have to start there... and went with the only answer that seemed possible.

Glad my semi-educated guess hit it.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 7

paul.knibbs (3/19/2012)
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. ... )

+1

In fact, I still do not understand, even after reading on it, why it does "escalation"

- "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...

So, why and where does the escalation comes from?

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

tilew-948340 (3/19/2012)
- "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...

Any data modification requires the locks be held until the transaction is complete, so until that last row completes the update, all locks must be held.

It's read locks under read committed isolation that release the locks as soon as the row has been processed.

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

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 9

GilaMonster (3/20/2012)
tilew-948340 (3/19/2012)
- "Rowlock" locks the row being updated and (what I think) releases the lock once the update is done.. It loops for the 7500 rows to be updated, locking and unlocking the rows...
Any data modification requires the locks be held until the transaction is complete, so until that last row completes the update, all locks must be held.
It's read locks under read committed isolation that release the locks as soon as the row has been processed.

Oh...transaction.. ya... makes sense now :doze:

Thank you!

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 10

Good simple question, but the explanation could have been better.

http://brittcluff.blogspot.com/

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 11

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

Of coarse he does!

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell