SQL Server Deadlocks

In this post we will take a look at how deadlocks occur in SQL Server.

Deadlocks are when two or more tasks are trying to lock the same resource. For example, if you have a stored procedure that locks a table, and another stored procedure is also trying to access that table, you may end up with a permanent deadlock situation. SQL Server has a process that detects if there is a deadlock occurring, and if so it will end one of the tasks, choosing it as the “victim”.

Consider the following scenario:

Let’s say you have 2 tables, Table A and Table B. Each table contains 2 fields, an Id and a Description:

The tables are populated with one row of data like below:

Now let’s assume you have 2 processes, Process A and Process B. These could be stored procedures, for example, as a transaction calling a SQL update like below:

-- Process A
BEGIN TRANSACTION

-- Update Table A
UPDATE TableA
SET [Description] = 'Updated'
WHERE Id = 1

-- Update Table B
UPDATE TableB
SET [Description] = 'Updated'
WHERE Id = 1

COMMIT TRANSACTION

-- Process B
BEGIN TRANSACTION

-- Update Table B
UPDATE TableB
SET [Description] = 'Updated'
WHERE Id = 1

-- Update Table A
UPDATE TableA
SET [Description] = 'Updated'
WHERE Id = 1

COMMIT TRANSACTION

Process A will update Table A, then Table B. Process B will update Table B, then Table A.

Now, let’s say these two processes are run at the same time. What can happen, is Process A will put a lock on Table A as it updates it. Process B will put a lock on Table B as it updates it. This is good so far.

Next, Process A will try to update Table B. However, Process B has a lock on Table B. So, it will wait for the lock to be removed.

Now, Process B will try to update Table A. However, Process A has a lock on Table A. So, it will also wait for the lock to be removed.

The two processes are now waiting for each other, and they are therefore deadlocked. SQL Server will then decide who is the deadlock “victim” based on a process that runs every 5 seconds by default (via process LOCK_MONITOR). If the process finds a deadlock, it will run every 100 milliseconds and then return to every 5 seconds once no more deadlocks occur.

Let’s run the code above to see this. If we run the first update statements with the transaction we see the tables are updated:

Next if we run the 2nd update statement from Process A, it will wait until it can get an exclusive lock:

Now, if we run the 2nd statement from Process B, we get the message:

Msg 1205, Level 13, State 45, Line 24
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

You can see Process A has completed, and Process B was chosen as the victim and was rolled back:

Finally, we need to run COMMIT TRANSACTION on Process A to commit and remove the lock on Tables A and B.

SQL Server has a method to determine which process to choose as the deadlock victim. You can set in your code the priority using the statement  SET DEADLOCK_PRIORITY and indicate a number between -10 and 10, or LOW, NORMAL and HIGH.

In future posts we will look at how to troubleshoot and avoid deadlocks.

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin