SqlException - Transaction (Process ID XX) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I’m currently migrating data between 2 systems. Therefore I build a small migration tool (using the great TPL Dataflow library).
While everything worked fine during development, I noticed that the migration failed on production with the following exception:
'Microsoft.Data.SqlClient.SqlException' in System.Private.CoreLib.dll ("Transaction (Process ID 153) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.")
Before I showed you how I fixed the problem, let me first give you some hints on how to investigate this issue.
Investigating a deadlock
The first thing I did was opening up my SQL Server Management Studio and checking the ‘Resource locking statistics by object’ report (check this link if you don’t know where to find this report):
In the report above I could see that both the doc.Document and doc.DocumentInfo tables were recently locked. That made sense as it are these 2 tables that are involved in the migration process. I could even drill down a little deeper and see the connectionstring user involved.
But now we still need to know what exactly was causing the deadlock. Therefore you can take the following steps:
- In SQL Server Management Studio, go to
- Management –> Extended Events –> system_health
- Right click on the package0.event_file and choose View Target Data from the context menu:
- Now you get a long list of events. Search for an xml_deadlock_report event and click on it:
- When you switch to the Deadlock view you get a nice visualization on the exact cause of the deadlock:
Fixing the deadlock
In this case the deadlock was caused because I was doing update statements to the same table in parallel.
Inside the update statement I was using a foreign key value (DocumentId) that was not indexed and resulted in a page lock.
UPDATE DOC.DocumentInfo SET DocumentKey = @DocumentKey WHERE DocumentId=@Id
I fixed it by changing the UPDATE query to use the primary key instead:
UPDATE DOC.DocumentInfo SET DocumentKey = @DocumentKey WHERE DocumentInfoId=@Id
More information
Dataflow (Task Parallel Library) - .NET | Microsoft Learn
Snapshot Isolation in SQL Server - ADO.NET | Microsoft Learn
SQL Server Timeouts–Find possible (dead)locks (bartwullems.blogspot.com)