Why do we need to update locks in SQL Server and SQL Server Update locks?

Why do we need to update locks in SQL Server and SQL Server Update locks?

Every time I explain the Locking and Blocking problems in SQL Server: Why do we need to update the locks in SQL Server? Before explaining the specific causes, I would like to introduce how the Update (U) Lock works when it is obtained according to its compatibility Lock.

Generally, when an UPDATE statement is executed, SQL Server uses the Update Lock ). If you view the corresponding execution plan, you will see that it contains three parts:

Read data
Calculate New Value
Write Data

In Part 1 of the query plan, SQL Server initially reads the data to be modified and obtains the Update Locks for each record ). In the last part of the query plan, when data is modified, these Update Locks are converted to Exclusive Locks (X )). This method produces the same problem: in the 1st phase, why does SQL Server obtain the Update Locks instead of the Shared Locks ). Generally, when you read data through the SELECT statement, the Shared lock (Shared (S) Locks) is enough. Why is the difference between the current update query plan? Let's analyze it in detail.

Deadlock Avoidance)
First, in the update query plan, update locks are used to avoid deadlocks. Assume that multiple update query plans obtain the Shared lock (S) Locks in the 1st phase of the plan, and then when the data is last modified in the 3rd phase of the query plan, what happens when these Shared Locks are converted to Exclusive Loks:

The 1st queries cannot be converted to the exclusive lock because the 2nd queries have obtained the shared lock.
The 2nd queries cannot be converted to the exclusive lock because the 1st queries have obtained the shared lock.

This is one of the main reasons. Why does the relational database engine introduce update locks to avoid specific deadlocks. One update lock is only compatible with one shared lock, but not with another update or exclusive lock. Therefore, the deadlock can be avoided. It is impossible for two update query plans to run concurrently. In the 1st phase of the query, the 2nd queries will wait until the update lock is obtained. An undisclosed Study of System R also shows how to avoid such significant deadlocks. System R does not apply any update locks to avoid deadlocks.

Increased concurrency

The update lock is not obtained in stage 1st. It is also visible to directly obtain the exclusive lock in this phase. This will overcome the deadlock issue because the exclusive lock is incompatible with another exclusive lock. However, the problem with this method is that concurrency is limited, because no other SELECT queries can read data with exclusive locks. Therefore, update the lock because it is compatible with the traditional shared lock. In this way, other SELECT queries can read data, as long as the update lock has not been converted into an exclusive lock. As a side effect, this will improve the concurrency of concurrent queries.

In previous studies, update locks were calledAsypolicric Lock ).In the update lock context, the 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 the shared lock as an asymmetric lock. Update locks are symmetric, that is, update locks and shared locks are mutually compatible. This will provide the overall concurrency of the system, because blocking is not introduced in two lock-type keys.

Summary
In today's article, I have introduced you to the shared locks and why they need shared locks. As you can see in relational databases, update locks are strongly required, because otherwise, deadlocks will occur and concurrency will be reduced. I hope that you have a good understanding of update locks and how they are used in SQL Server.

The above is all the content of this article. I hope you will like it.