3

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

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

This question came from our site for professional and enthusiast programmers.

  • 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
4

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.

  • 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
  • 4
    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
1

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".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy