In SQL Server Why do we need to update the lock

Today I want to talk about a particular problem that I will encounter every time I talk about lock and block (Locking & Blocking) in SQL Server: Why do we need to update the lock in SQL Server? Before we explain the reasons for the specific needs, first I would like to introduce you to how the compatibility lock itself is addressed when updating lock (update (U) lock) is obtained.

In general, when an UPDATE statement is executed, SQL Server uses the update lock. If you look at the corresponding execution plan, you will see that it contains 3 parts

• Reading data
• Calculate new value
• Write Data

In the 1th part of the query plan, SQL Server initially reads the data to be modified, and an update lock is obtained on each record (update Locks). At the end of the 3rd part of the query plan, these update locks (update Locks) are converted to exclusive locks (Exclusive (X)) when the data is modified. The problem with this approach is the same: in the 1th phase, why SQL Server obtains an update lock (Locks) instead of a shared lock (Locks). Normally when you read data through a SELECT statement, the shared lock (S) Locks is sufficient. Why does the current update query plan have this difference? Let's take a detailed analysis.

Dodge Deadlock (Deadlock avoidance)

First in the update query plan, the update lock is used to avoid deadlock situations. Assuming that in the 1th phase of the plan, there are multiple update query plans to obtain a shared lock (GKFX (S) Locks), and then in the 3rd stage of the query plan, when the data is finally modified, these shared locks (Locks) are converted to exclusive locks (Exclusive Loks), What will happen:

• The 1th query cannot convert a shared lock to an exclusive lock because the 2nd query has acquired a shared lock.
• The 2nd query cannot convert a shared lock to an exclusive lock because the 1th query has acquired a shared lock.

This is one of the main reasons why the relational database engine introduced update locks to avoid specific deadlock scenarios. An update lock is compatible with only one shared lock, but not with another update or exclusive lock. As a result, the deadlock scenario can be avoided, and it should not be possible to run concurrently at the same time for 2 update query plans. In the 1th phase of the query, the 2nd query waits until an update lock is obtained. A non-public study of System R also shows how to avoid such significant deadlocks. System R does not utility any update locks to achieve the avoidance of deadlocks.

Elevated concurrency

In the 1th phase does not get the update lock, which is also the visible option to get the exclusive lock directly at this stage. This overcomes the deadlock problem because the exclusive lock is incompatible with another exclusive lock. However, the problem with this approach is that concurrency is restricted because no other select query can read data that currently has an exclusive lock. Therefore, the lock needs to be updated because this particular lock is compatible with the traditional shared lock. In this case, the other select query can read the data as long as the update lock has not been converted to exclusive lock. As a side effect, this increases the concurrency of the queries we run concurrently.

In the previously relational academic, the update lock is called an asymmetric lock (asymmetric lock). In the context of the update lock, this update lock is compatible with the shared lock, but vice versa: the shared lock is incompatible with the update lock. However, SQL Server does not implement shared locks as asymmetric locks. Update locks are symmetric (symmetric), meaning that update locks and shared locks are bidirectional compatible. This provides the overall concurrency of the system, as the 2 lock type keys do not introduce a blocking scenario.

Summary

In today's article I introduce you to shared locks and why you need to share them. As you can see in the relational database, it is strongly necessary to update the lock, because otherwise it will lead to deadlock and reduce concurrency. I hope that now you have a good understanding of update locks and how they are used in SQL Server.

Thanks for your attention!

Why we need to update locks in SQL Server