By: Sergey Gigoyan | 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.
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:
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:
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:
- Locking and Blocking Tips
- https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15
- https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
About the author
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
