Why do we need to update locks in SQL Server _mssql

Every time you talk about locks and blocks in SQL Server (Locking & Blocking): Why do we need to update locks in SQL Server? Before we explain the reasons for specific needs, first I want to introduce you to the next time when the update lock (lock) is obtained, according to its compatibility lock itself is how to deal with.

In general, when an UPDATE statement is executed, SQL Server uses an 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 (update Locks) is obtained on each record. In the last 3rd part of the query plan, when the data is modified, these update locks (update Locks) are converted to exclusive locks (Exclusive (X)). The problem with this method is the same: in the 1th phase, why does SQL Server get an update lock (update Locks) instead of a shared lock (Shared (S) Locks). Normally when you read data through a SELECT statement, shared locks (Share (S) Locks) are sufficient. Why does the current update query plan have this distinction? Let's analyze it in detail.

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

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

This is one of the main reasons why the relational database engine introduces an update lock to avoid a particular deadlock situation. An update lock is compatible with only one shared lock, but not another update or exclusive lock. As a result, deadlock situations can be avoided and 2 update query plans cannot be run concurrently. In the 1th phase of the query, the 2nd query waits until the update lock is obtained. An undisclosed study of System R also shows how to avoid such significant deadlocks. System R does not implement any update locks to avoid deadlocks.

Elevated concurrency

In phase 1th, the update lock is not obtained and the exclusive lock is also visible at this stage. This will overcome the deadlock problem because the exclusive lock is incompatible with another exclusive lock. The problem with this approach, however, is that concurrency is restricted because no other select query can read data that currently has an exclusive lock. It is therefore necessary to update the lock because this particular lock is compatible with the traditional shared lock. In this case, other select queries 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 our concurrent run queries.

In the previous relationship academic, update locks were called asymmetric locks (asymmetric lock). in the context of the update lock, this update lock is compatible with the shared lock, but the opposite is not: shared locks are incompatible with update locks. However, SQL Server does not implement shared locks as asymmetric locks. Update locks are symmetric (symmetric), meaning that update locks and shared locks are two-way compatible. This provides the overall concurrency of the system because the 2 lock type keys do not introduce a blocking case.

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

The above is the entire content of this article, I hope you can enjoy.