none
Update statement lock RRS feed

Answers

  • It depends.

    If the UPDATE statement qualifies a single row and SQL Server knows this beforehand (because the WHERE condition is on a unique index), only the single row will be locked.

    On the other hand, if you update all rows in the table, the lock will be taken out on the entire table.

    And then there are a whole number of cases in between and SQL Server decides what it thinks is the best. The locks could be taken out on row, page or table level. If it starts out with row locks, but find that it needs to lock many rows (I think 5000 is the number), it escalates to have the lock on table level.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 2, 2018 10:23 AM

All replies

  • It depends.

    If the UPDATE statement qualifies a single row and SQL Server knows this beforehand (because the WHERE condition is on a unique index), only the single row will be locked.

    On the other hand, if you update all rows in the table, the lock will be taken out on the entire table.

    And then there are a whole number of cases in between and SQL Server decides what it thinks is the best. The locks could be taken out on row, page or table level. If it starts out with row locks, but find that it needs to lock many rows (I think 5000 is the number), it escalates to have the lock on table level.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, December 2, 2018 10:23 AM
  • How have you written the UPDATE block?

    Without seeing that its hard to answer

    Is it wrapped within TRANSACTION block?

    What is your default isolation level?

    Local escalation may also happen sometimes as seen in below example

    https://www.sqlpassion.at/archive/2016/05/09/lock-escalations-do-they-always-happen/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, December 2, 2018 12:10 PM