SQL Server UPDATE lock and UPDLOCK Table Hints

By:   |   Updated: 2020-01-28   |   Comments (7)   |   Related: > Locking and Blocking


Problem

In SQL Server databases with actively running transactions, it is a common situation when more than one transaction tries to modify the same data simultaneously. In such instances, SQL Server deadlocks are often quite possible, which can be a real issue in terms of performance. This is because in the case of a deadlock, only the changes made by one of the transactions will be committed and all others will be rolled back. 

In this article, we will discuss how to acquire an UPDATE lock by using the UPDLOCK table hint in order to avoid deadlocks.

Solution

Before moving forward to discuss the UPDATE locks, let’s understand deadlocks.

Overview of SQL Server Deadlocks and Example

A deadlock is a situation when processes mutually block each other. To understand, assume that a transaction is trying to modify data that is being modified by another transaction. The second transaction, in turn, is trying to change data that is being modified by the first one. In other words, while the first transaction is waiting for the second one to complete (either commit its changes or roll back), the second is waiting for the completion of the first one.

Obviously, this situation cannot last infinitely, so eventually the SQL Server database engine will solve the problem. It has a mechanism of monitoring deadlocks and after finding them, it allows only one of the transactions to commit its changes. The other(s) becomes a victim of the deadlock, which means that all changes made by these concurrent transactions are rolled back. Therefore, locks are released allowing the “winner” transaction to make its changes and commit. Which transaction will be committed and which will be rolled back is decided by the SQL Server engine.

As you might have already guessed, having frequent deadlocks in a system can really affect performance. The reason for this is that if a transaction becomes a deadlock victim, time and resources used by it can be considered as wasted as all its changes are rolled back.  Thus, designing a system where deadlocks are less possible is very important.

In this article, we are going to learn how the usage of UPDATE locks can help to prevent deadlocks.

First, we will create a test environment with two global temporary tables and sample data as follows:

--Two global temp tables with sample data for demo purpose
CREATE TABLE ##TableA (
    ID INT IDENTITY,
    Val CHAR(1)
)
GO
 
INSERT INTO ##TableA (Val)
VALUES ('A'), ('B')
GO
 
CREATE TABLE ##TableB(
        ID INT IDENTITY,
        Val CHAR(1)
)
GO
 
INSERT INTO ##TableB (Val)
VALUES ('C'), ('D')
GO

In order to better understand the reasons of deadlocks, we will simulate a situation when a deadlock happens.

In SQL Server Management Studio (SSMS), we open two query windows and copy the code below in the first window:

-- run this in query window 1
BEGIN TRANSACTION
 
--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

The code below is copied to the second window:

-- run this in query window 2
BEGIN TRANSACTION
 
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

Immediately after executing the first query, we execute the second one.

As we can see below, the first transaction succeeds and the second one becomes a deadlock victim as the error message suggests. Therefore, the changes made by the first are saved, and the changes by the second are rolled back.

query results

Well, let’s understand what happens.

When executing the first query, it starts to update ##TableA. The second transaction, started immediately after the first, updates ##TableB. Then, the first transaction is trying to update ##TableB, but changes in this table are not committed by the second transaction yet. Therefore, to update this table, the first transaction waits for the second to complete. Meanwhile, the second transaction is trying to update ##TableA, which was already modified, but not committed by the first transaction. As a result, the second transaction, in its turn, waits for the completion of first one. Hence, they mutually block each other and a deadlock occurs.

Here is some information about locks that SQL Server uses:

  • Shared lock (S) is used to read data. Although a shared lock does not prevent the concurrent transactions to read the same data (placing a shared lock on the same resource), it prevents the modification of that data by the concurrent transactions.
  • Exclusive lock (X) is requested to modify data. If an exclusive lock is placed on a resource, other transactions cannot even read that data (unless that transaction uses the READUNCOMMITTED isolation level or NOLOCK hint is used allowing dirty reads). When a transaction is going to modify data, a Shared lock is used to read the data. After that, an Exclusive lock is placed to modify that data. When two transactions are waiting on each other to convert Shared locks on resources to Exclusive locks, a deadlock occurs.
  • Update lock (U) is used to avoid deadlocks. Unlike the Exclusive lock, the Update lock places a Shared lock on a resource that already has another shared lock on it. Also, it is possible to place a shared lock on a resource that has an update lock. When the transaction is ready to make its changes, the update lock converts to an exclusive lock. This behavior allows prevention of deadlocks as if an update lock is placed on a resource, the concurrent transactions will wait for the first one to complete the changes and only after that read and modify the data. The UPDLOCK tablehint is used to impose an update lock on a resource until the transaction completes. Thus, if a transaction reads data which potentially can be updated in that transaction, and there are concurrent transactions that can try to change the same data, the UPDLOCK hint can be used while reading this data in order to avoid deadlocks.

Using UPDLOCK to Avoid a SQL Server Deadlock

Now, let’s test this behavior in practice.

We have modified the first query and added a SELECT statement that retrieves the same data which will be modified in this transaction. Additionally, we get the process ID for this transaction:

-- run this in query window 1
BEGIN TRANSACTION
 
SELECT @@SPID AS FirstTransactionProcessID
 
SELECT ID 
FROM ##TableB WITH (UPDLOCK)
WHERE ID=1
 
 --1
UPDATE ##TableA 
SET Val = 'E'
WHERE ID = 1 
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB 
SET Val= N'G'
WHERE ID = 1
------------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

In the second transaction, we have also modified the code and used the sp_lock procedure to monitor the update lock:

-- run this in query window 2
BEGIN TRANSACTION
 
--2
SELECT @@SPID AS SecondTransactionProcessID
EXEC sp_lock
 
UPDATE ##TableB 
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

In this case, no deadlock happens, and both transactions are committed successfully.

Moreover, the changes by the first transaction are replaced by the changes made by the second transaction since this is committed last. As a row in ##TableB will be updated in the first transaction, the SELECT statement at the beginning of the transaction using an UPDLOCK hint, will guarantee the placement of an update lock when needed. Therefore, the second transaction, unlike the previous example, will not be able to access that row and will wait for the first one to complete. After the first transaction is committed, the second make its changes and is committed as well:

query results

As you might have noticed, we didn’t select the row from ##TableA with an UPDLOCK hint. This is because the first transaction accesses ##TableA before the second one tries. Thus, placing an UPDATE lock on that row is not necessary for the first transaction. In order to commit the changes, the first transaction needs to update the row in ##TableB. Due to the defined order, however, the second transaction accesses ##TableB earlier than the first one. Therefore, placing an UPDATE lock only on the row of ##TableB is enough to avoid a deadlock. The update lock placed on the updated row of ##TableB can be seen in the result of the execution of the sp_lock procedure at the beginning of the second transaction. It is highlighted in red in the picture above.

Using the object_id from that results above, we can get the object name:

USE tempdb
GO
 
SELECT OBJECT_NAME (965578478)

We can see that the object is ##TableB:

select object info
Conclusion

The examples above are very simple cases illustrating the behavior of the update lock and the usage of the UPDLOCK hint. In real-world examples, the cases are often more complicated. However, understanding the basics of this lock type and hint can be very helpful in developing much more complicated solutions.

All in all, deadlocks can cause serious problems in terms of database performance, especially for systems overloaded by many concurrent transactions. The UPDATE lock can be used to prevent deadlocks. If data is retrieved for modifying the same transaction, and potential deadlocks are possible due to the other transactions using the same data, selecting data with the UPDLOCK hint could be reasonable. This will guarantee that other transactions will not be able to place shared locks on that data (that are going to be replaced by an exclusive lock) and, in this way, will prevent deadlocks.

Next Steps

For more information, please use the links below:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2020-01-28

Comments For This Article




Tuesday, September 13, 2022 - 2:31:15 PM - Sage Arbor Back To Top (90475)
sp_lock is being depracated

mentioned in notice at top of https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-ver16

suggest to use sys.dm_tran_locks, see below
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver16

Tuesday, July 27, 2021 - 5:03:15 PM - Sergey Gigoyan Back To Top (89059)
Aleksey,
In our example, the deadlock happens when two transactions are trying to access the same resource. There are two tables in the example but as you can see, two transactions are trying to access these tables in the opposite order. As the result, they wait for each other to commit their changes. Thus, a deadlock occurs.

Tuesday, July 27, 2021 - 4:07:34 PM - Aleksey Back To Top (89058)
very confusing, correct me if I'm wrong, but MSDN says UPDATE locks are to prevent the type of deadlock when 2 transactions acquire a shared lock on the _same_ record in the _same_ table and then both try to update this record and cannot because of the other transaction's shared lock. So very confusing to have 2 tables in the example and the explanation itself is very confusing. MSDN article: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15 (Update locks) section

Thursday, March 11, 2021 - 8:19:20 PM - Sergey Gigoyan Back To Top (88381)
Good finding, Leo Miller. Actually, the shared lock is compatible with the update lock. The article will be updated accordingly.

Thursday, March 11, 2021 - 5:36:28 PM - Leo Miller Back To Top (88380)
Are you sure a Shared Lock can't be put on a record that has an Update Lock? I did a test on this because I have an application using a lot of UPDLOCK hints, and found that if connection 1 had an open transaction and did a select with UPDLOCK, connection 2 could still read the same data i.e. take out a shared lock.
On 1:
begin tran
select * from tst_table with (UPDLOCK) where TestTableID = 1

On 2
begin tran
select * from tst_table where TestTableID = 1

2 still returns data.
I only get blocking if 2 also had the UPDLOCK hint, or 1 updates the data but doesn't commit.


Monday, January 25, 2021 - 7:27:13 AM - Arlvin Back To Top (88098)
Thanks for the clear and simple explanation. Totally got it!

Thursday, August 6, 2020 - 12:35:57 PM - Lalitha Back To Top (86245)
Thanks for this great explanation














get free sql tips
agree to terms