What is Default Locking level in SQL Server 2008 or 2008 R2?

Question

What is Default Locking level in SQL Server 2008 or 2008 R2?

SQlLogic

SSCrazy Eights

Points: 9686
More actions
November 1, 2013 at 12:44 pm

#278889

Hi guys
Like to know what kind of default locking level in sql server is ?
Page ,table or row level?
anyway to check it ?
Thanks
--------------------------------------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Depends on how much data is going to be affected. It's not fixed. Couple of rows, row locks, fair portion of the table, probably page locks, most of the table, likely a table lock.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Take a look at this article.

http://technet.microsoft.com/en-us/library/ms189849.aspx

Then scroll down and read about lock escalation. That should answer your questions pretty well.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

assuming you havent disabled row or page locks on the index it would generally start with a row lock but as Gail said it depends on how much data is being processed

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 4

Everything is default configuration.. nothing is changed.. so my ext que is when page level locking is happens if it default row level locking?

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

Locking isn't by default at the row level. It depends how much data you're retrieving. SQL can choose to lock at the row level, page level or table level.

Did you read the referenced article?

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

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 6

I read thatnk you Gail.. I got my answer.

Have a good Day