Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I am using SQL Server 2008.

  • What is the default lock behaviour with UPDATE?

    • NOLOCK?
    • ROWLOCK?
    • PAGLOCK?

  • How can I tell what the current level of lock is for a table?

Thanks, Bruce

share|improve this question
1  
For an UPDATE, SQL Server must take first an UPDATE (U) lock (while reading the existing data), which then needs to be escalated to an exclusive lock (X) on that row. This is the default behavior for a single update - if you're batch updating, your row locks might get escalated to table-level locks – marc_s Dec 14 '11 at 20:41

migrated from stackoverflow.com Dec 14 '11 at 20:46

2 Answers

up vote 1 down vote

The default behavior for update is to first take a row-level update (U) lock to read the data, followed by an exclusive (X) lock to write it.

You can see the current lock status of all objects in your DB by running "exec sp_lock".

share|improve this answer
up vote 4 down vote

It really depends on how much you're updating. Locks will escalate as the size of the query increases. If many rows within the same page will be modified, SQL Server will escalate to a PAGLOCK. If many pages will be modified, it will escalate to a TABLOCK.

share|improve this answer
And also depends on ALLOW_PAGE_LOCK, ALLOW_ROW_LOCKS options on the object. – Martin Smith Dec 14 '11 at 19:36
In case anyone is wondering, HoBT means "Heap or B-Tree": in other words, a table or an index. – Andomar Dec 14 '11 at 19:44
2  
Slight correction: row/key locks are never escalated to page locks. They are only escalated directly to table locks. However, the database engine may mix row and page locks for a single statement. msdn.microsoft.com/en-us/library/ms184286.aspx – db2 Dec 15 '11 at 18:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.