【SQL Lock -1】Why do we need UPDATE Locks in 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 (v. 取得; 获得( acquire的过去式和过去分词 ); , and how the lock itself behaves regarding( prep.关于; 就…而论; 至于;) its compatibility( n.适合; 互换性; 通用性; 和睦相处;).

In general an Update Lock is used in SQL Server when performing an UPDATE statement. When you look at the underlying( adj.潜在的，含蓄的; 基础的; ) query plan, you can see that such a plan always consists( v. 包括; 由…组成) of 3 parts:

• Reading data
• Calculating new values
• Writing data

When SQL Server initially( adv.最初; 开始;) reads the data to be changed in the first part of the query plan, Update Locks are acquired on the individual( adj.个人的; 个别的; 独特的;) records. And finally these Update Locks are converted to Exclusive (X) Locks when the data is changed in the third part of the query plan. The question that arrises( vi. 出现; 产生; 起身，起立; 起源于，产生于;) with this approach is always the same: why does SQL Server acquire Update Locks instead of Shared (S) Locks in the first phase( n. 阶段; )? When you normally read data through a SELECT statement, a Shared Lock is also good enough. Why is there now a different approach with UPDATE query plans? Let’s have a more detailed look at it.

 Deadlock Avoidance

First of all UPDATE Locks are needed to avoid deadlock situations in UPDATE query plans. Let’s try to imagine what happens when multiple UPDATE query plans acquire Shared Locks in the first phase of the plan, and afterwards convert these Shared Locks to Exclusive Locks when the data is finally changed in the third phase of the query plan:

• The 1st query can’t convert the Shared Lock to an Exclusive Lock, because the 2nd query has already acquired a Shared Lock.
• The 2nd query can’t convert the Shared Lock to an Exclusive Lock, because the 1st query has already acquired a Shared Lock.

That approach would lead to a traditional deadlock situation in a relational database:

That’s one of the main reasons why implementers of relational database engines have introduced Update Locks to avoid that specific deadlock situation. An Update Lock is only compatible( adj.兼容的，相容的; 和谐的，协调的; ) with a Shared Lock, but isn’t compatible with another Update or Exclusive 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( adj.著名的; 突出的，杰出的; 突起的;) one. System R was initially implemented without any Update Locks.

Improved Concurrency( n. 并发（性）;)

Instead of acquiring an Update Lock during the 1st phase, it would be also a viable option to acquire an Exclusive Lock directly in that phase. This will also overcome the deadlock problem, because an Exclusive Lock is not compatible with another Exclusive Lock. 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(adv.唯一地; 专门地，特定地;) locked. Therefore there is also the need for the Update Lock, because this specific lock is compatible with the traditional Shared Lock. As a result this means that other SELECT queries can read data, as long as individual Update Locks are not yet converted to Exclusive Locks. As a side-effect this will improve the concurrency of our parallel( adj.平行的; 相同的，类似的; ) running queries. 

 In traditional relational literature an Update Lock is a so-called Asymmetric( adj.不对称的，不匀称的;) Lock. In the context of the Update Lock that means that the Update Lock is compatible with the Shared Lock, but not vice-versa(反之亦然): the Shared Lock is not compatible with the Update Lock. But SQL Server doesn’t implement the Update Lock as an asymmetric one. The Update Lock is a symmetric one, which means that Update and Shared Locks are compatible in both directions( n. 方向; ). This will also improve the overall(adj. 全部的; 全体的; ) concurrency of the system, because it doesn’t introduce( vt. 介绍; 引进;) blocking situations between both lock types.

Summary

In todays blog posting I gave you an overview of Update Locks in SQL Server, and why they are needed. As you have seen there is a really strong need for Update Locks in a relational database, because otherwise it would yield( vt. 屈服，投降; ) 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.