Problem
In SQL Server databases with actively running transactions, it is common for multiple transactions to try to modify the same data simultaneously. In such instances, SQL Server deadlocks are often possible, which can be a real issue in terms of performance. This is because, in the case of a deadlock, SQL Server commits only the changes from one of the transactions while all others roll 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, let’s assume a transaction tries to modify data that another transaction is modifying. The second transaction, in turn, tries to change data that the first one is modifying. 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 for monitoring deadlocks. After finding them, it allows only one of the transactions to commit its changes. The other(s) becomes a victim of the deadlock, meaning that all changes by these concurrent transactions roll back. Therefore, locks release to allow the “winner” transaction to make its changes and commit. The SQL Server engine decides which transaction to commit and which to roll back.
As you might have already guessed, having frequent deadlocks in a system can really affect performance. If it becomes a deadlock victim, all its changes roll back, wasting time and resources. 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')
GOIn 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=1Copy the code below in 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=1Immediately after executing the first query, we execute the second one.
As seen below, the first transaction succeeds and the second one becomes a deadlock victim as the error message suggests. Therefore, it saves the changes made by the first and rolls back the changes by the second.
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 tries 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 tries to update ##TableA (already modified, but not committed by the first transaction). As a result, the second transaction, in its turn, waits for the completion of the 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): Reads data. Although a shared lock does not prevent the concurrent transactions from reading 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): Modifies 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 needs to modify data, a Shared lock can read it. After that, place an Exclusive lock 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): Avoids 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 the 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 table hint imposes an update lock on a resource until the transaction completes. Thus, if a transaction reads data that can potentially be updated in that transaction, and concurrent transactions try to change the same data, using the UPDLOCK hint while reading this data can avoid deadlocks.
Using UPDLOCK to Avoid a SQL Server Deadlock
Now, let’s test this behavior in practice.
We modified the first query and added a SELECT statement that retrieves the same data that 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=1In 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=1In this case, no deadlock happens, and both transactions commit successfully.
Moreover, the second transaction changes replace the first transaction changes since they commit last. As a row in ##TableB updates in the first transaction, the SELECT statement at the beginning of the transaction using an UPDLOCK hint guarantees the placement of an update lock when needed. Therefore, the second transaction, unlike the previous example, cannot access that row and will wait for the first one to complete. After the first transaction commits, the second makes its changes and commits 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. 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 is shown in the execution of the sp_lock procedure result at the beginning of the second transaction. See the red rectangle in the image 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 database performance problems, especially for systems overloaded by many concurrent transactions. The UPDATE lock can 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 cannot place shared locks on that data (those to be replaced by an exclusive lock) and will prevent deadlocks.
Next Steps
For more information, please use the links below:
