But if a lock is acquired after establishing a savepoint, the lock is released immediately if the savepoint is rolled back to.
As you have seen there is a really strong need for Update Locks in a relational database, because otherwise it would yield to deadlock situations and decreased concurrency.I hope that you now have a better understanding of Update Locks, and how they are used in SQL Server. cannot safely be executed concurrently with other operations on the same table, so it obtains an exclusive lock on the table to enforce that.) To examine a list of the currently outstanding locks in a database server, use the ; the names of the lock modes are historical.To some extent the names reflect the typical usage of each lock mode — but the semantics are all the same.(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.
But the problem with that approach is limited concurrency, because in the mean time no other SELECT query can read the data that is currently exclusively locked.
Therefore there is also the need for the Update Lock, because this specific lock is compatible with the traditional Shared 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.
An unpublished study of System R also showed that this kind of deadlock was the most prominent one.