Shulou(Shulou.com)05/31 Report--

Why do we need to update locks in SQL Server? I believe many inexperienced people are at a loss about this. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

The question I encounter every time I talk about Locking and Blocking in SQL Server: why do we need to update locks in SQL Server? Before we explain the reasons for the specific needs, first of all, I would like to show you how to deal with the update lock (Update (U) Lock) when it is acquired according to its compatibility lock itself.

In general, SQL Server uses an update lock (Update Lock) when executing a UPDATE statement. If you look at the corresponding execution plan, you will see that it contains three parts:

Read data, calculate new values, write data

In part 1 of the query plan, SQL Server initially reads the data to be modified and obtains an update lock (Update Locks) on each record. In the final third 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 first phase, why does SQL Server acquire an update lock (Update Locks) instead of a shared lock (Shared (S) Locks). Usually when you read data through the SELECT statement, the Shared (S) Locks is enough. Why is there such a difference in the current update query plan? Let's analyze it in detail.

Avoid deadlock (Deadlock Avoidance) first update the lock in the update query plan to avoid the deadlock situation. Suppose that in phase 1 of the plan, multiple update query plans acquire shared locks (Shared (S) Locks), and then in phase 3 of the query plan, when the data is finally modified, what happens when these shared locks (Shared Locks) are converted to exclusive locks (Exclusive Loks):

The first query cannot convert a shared lock to an exclusive lock because the second query has already acquired the shared lock. The second query cannot convert a shared lock to an exclusive lock because the first query has already acquired the shared lock.

This is one of the main reasons why the relational database engine introduces update locks to avoid specific deadlock situations. An update lock is compatible with only one shared lock, but not with another update or exclusive lock. Therefore, the deadlock situation can be avoided because it is impossible for two update query plans to run concurrently. In the first phase of the query, the second query waits until the updated lock is acquired. An unpublished study by System R also shows how to avoid such significant deadlocks. System R does not use any update locks to avoid deadlocks.

Elevated concurrency

No updated locks are obtained in stage 1, and it is also a visible option to acquire exclusive locks directly at this stage. This overcomes the deadlock problem because the exclusive lock is not compatible with another exclusive lock. The problem with this approach is that concurrency is limited because there are no other SELECT queries that can read data that currently has exclusive locks. Therefore, the lock needs to be updated because this particular lock is compatible with traditional shared locks. This way, other SELECT queries can read the data, as long as the update lock has not been converted to an exclusive lock. As a side effect, this increases the concurrency of running queries concurrently.

In previous academic relationships, update locks were so-called asymmetric locks (Asymmetric Lock). In the context of an update lock, this update lock is compatible with the shared lock, but vice versa: the shared lock is not compatible with the update lock. However, SQL Server does not implement shared locks as asymmetric locks. The update lock is symmetric, which means that the update lock and the shared lock are bidirectional compatible. This provides overall concurrency of the system because blocking is not introduced in the two lock type keys.

As you can see in relational databases, there is a strong need to update locks, because otherwise it will lead to deadlocks and reduce concurrency.

After reading the above, do you understand why we need to update locks in SQL Server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.