Sql lock table while updating
Row-level locks do not affect data querying; they block only .A shared lock does not prevent other transactions from acquiring the same shared lock.Let’s try to imagine what happens when multiple UPDATE query plans acquire Shared Locks in the first phase of the plan, and afterwards convert these Shared Locks to Exclusive Locks when the data is finally changed in the third phase of the query plan: That approach would lead to a traditional deadlock situation in a relational database: That’s one of the main reasons why implementers of relational database engines have introduced Update Locks to avoid that specific deadlock situation.An Update Lock is only compatible with a Shared Lock, but isn’t compatible with another Update or Exclusive Lock.Therefore a deadlock situation can be avoided, because 2 UPDATE query plans can’t run concurrently at the same time.The 2nd query will just wait until the Update Lock can be acquired in the 1st phase of the query plan.
The Update Lock is a symmetric one, which means that Update and Shared Locks are compatible in both directions.(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) Today I want to talk about a specific question that I almost get every time when I teach about Locking & Blocking in SQL Server: Why does SQL Server need to have Update Locks?Before we go down to the details of why they are needed, I first want to give you a basic overview of when an Update (U) Lock is acquired, and how the lock itself behaves regarding its compatibility.The only real difference between one lock mode and another is the set of lock modes with which each conflicts (see Table 13-2).Two transactions cannot hold locks of conflicting modes on the same table at the same time.
But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to.