Why does this script cause a SQL deadlock often?

how to resolve deadlock in sql server
how to avoid deadlock in sql server
how to handle deadlock in sql server stored procedure
query to find deadlock in sql server 2016
deadlock example in sql server
sql server deadlock history
what causes sql deadlocks
how to avoid deadlock in update sql server

I am running a background task within a web job in Azure every hour. Sometimes (it appears to be a little more than 50% of the time, the code will choke (with a deadlock error) on this particular piece of code:

foreach (var ownerToProcess in activeOwnersWithMessageArchiving)
{
    foreach (var extension in extensions)
    {
        using (var db = new SqlConnection(connectionString))
        {
            db.Execute(@"
UPDATE T_MESSAGESTARTER 
   SET Started=@started,Completed=NULL 
 WHERE OwnerId=@ownerId AND ExtensionId=@extensionId;

if @@ROWCOUNT=0
  INSERT INTO T_MESSAGESTARTER (OwnerId,ExtensionId,Started) 
  VALUES (@ownerId,@extensionId,@started)
", new { ownerId = ownerToProcess, extensionId = extension, started = DateTimeOffset.Now });
        }
    }
}

It's a simple update/insert statement. I "believe" I am using row-level blocking too. This is not inside a transaction. Also, there are around 60 ownerToProcess items in the top-level. And each one of those has between 5-60 extension items in the inner loop (in the code above). And that makes for around 4000 executions of this SQL statement throughout each run. Each @owner/@extension combo (in the WHERE clause) is unique.

Sometimes it will run all the way through without errors. But sometimes I will get the deadlock error on one of the executions of the SQL statement. What could be causing this? Is it because I have the UPDATE/INSERT structure in the SQL statement? Or could Dapper be doing something funny?

Another thing to note: the T_MESSAGESTARTER table in question does not have a primary key. Could that be causing this issue?

the update locks the "table" even if no row is actually updated (when row does not exist). Depending on the concurrency the following most likely is safe (if two processes will never handle the same owner&extension then it will work)

IF EXISTS(SELECT ...  FROM T_MESSAGESTARTER WHERE OwnerId=@ownerId....)
BEGIN
UPDATE T_MESSAGESTARTER 
   SET Started=@started,Completed=NULL 
 WHERE OwnerId=@ownerId AND ExtensionId=@extensionId;
END
ELSE 
  INSERT INTO T_MESSAGESTARTER (OwnerId,ExtensionId,Started) 
  VALUES (@ownerId,@extensionId,@started)
END

Why does this script cause a SQL deadlock often?, the update locks the "table" even if no row is actually updated (when row does not exist). Depending on the concurrency the following most� And that makes for around 4000 executions of this SQL statement throughout each run. Each @owner / @extension combo (in the WHERE clause) is unique. Sometimes it will run all the way through without errors.

It doesn't need to be the PK but a composite unique index on ownerId and extensionId, ideally clustered, will optimize the update query. That might mitigate deadlocks (depending on the processes involved) by touching the minimal amount of data.

The Cause of Every Deadlock in SQL Server, The flag will be enabled and will start logging detailed deadlock information to the error log. The details from this trace flag are much easier to� It creates an infinite wait - and then a deadlock. Why does SQL Server chose a clustered index scan just for this particular SQL (steps #7 and #8), this is because with this small amount of data the index “IX_City_Not_Covering_Index” is not selective enough and that’s why SQL Server chose to do the scan. Actually, it is a very good choice.

No need to open a connection in a double foreach iteration.

using (var db = new SqlConnection(connectionString))
{
  foreach (var ownerToProcess in activeOwnersWithMessageArchiving)
  {
    foreach (var extension in extensions)
    {

            db.Execute(@"
UPDATE T_MESSAGESTARTER 
   SET Started=@started,Completed=NULL 
 WHERE OwnerId=@ownerId AND ExtensionId=@extensionId;

if @@ROWCOUNT=0
  INSERT INTO T_MESSAGESTARTER (OwnerId,ExtensionId,Started) 
  VALUES (@ownerId,@extensionId,@started)
", new { ownerId = ownerToProcess, extensionId = extension, started = DateTimeOffset.Now });
    }
  }
}

And FYI, there exists also the MERGE statement. Which is a standard SQL that can also do upserts.

The Cause of Every Deadlock in SQL Server, This point is often overlooked. Blocking is to be expected in a relational database . Deadlocks are not. A typical response to blocking and� @Dinesh - once you figure out the SQL statements that are causing the deadlock you need to figure out where these are being called and when they are called. You would need to make sure you do things in the same order to eliminate deadlocks or once you make a data change commit the changes so it doesn't cause other commands to be deadlocked.

SQL Server performance monitoring: dealing with deadlocks, Script � SQL Dependency Tracker � SQL Scripts Manager � SQL Index Manager � SQL Data Catalog Transaction (Process ID 62) was deadlocked on lock resources with another The result is a 'circular chain' where no session can complete, and so SQL With SQL Server 2008 and later there are much better ways. Figure 1: Image of a cycle lock. Conversion locks deadlock definition. A conversion deadlock occurs when a thread tries to convert a lock from one type to another exclusive type but is unable to do so because another thread is already also holding a shared lock on the same resource.

Handling Deadlocks in SQL Server - Simple Talk, In fact, SQL Server is designed to detect and resolve deadlocks Many of the same issues that cause severe blocking in the database, such as poor in a manner that makes it much easier to identify the deadlock victim, as well is to first create the trace in SQL Profiler, and then export it to a script using� Why does this script cause a SQL deadlock often? I am running a background task within a web job in Azure every hour. Sometimes (it appears to be a little more than 50% of the time, the code will choke (with a deadlock error) on this particular

What are SQL Server deadlocks and how to monitor them, These actions would lead to either a lower occurrence frequency or a total disappearance of This second case is commonly referred to as “blocking”. Here is the equivalent T-SQL script for creating Collect-Deadlock event:� In the article on Deadlock Analysis and Prevention, we saw how deadlock occurs. We saw that SQL Server selects one of the processes involved in the deadlock as deadlock victim. In this article, we will look at the criteria on which SQL server selects a deadlock victim. Why one process is selected as deadlock victim and not the other. Deadlock

Comments
  • We need to see your deadlock graph to know what resources are deadlocking and why. Its not something that can be determined just from the code.
  • SUGGESTION: Consider moving these two lines into a stored procedure, and enable AUTOCOMMIT_SP_MODE=ON within the stored procedure. I'll bet there's a very good chance it could eliminate the deadlocks. Easy to do; easy to empirically verify ;)
  • @DaleK I'm not a SQL expert and I don't know how to generate a deadlock graph unfortunately.
  • @MattSpinks if you google obtaining a deadlock graph you'll find there is a fairly straight forward query to obtain it - I just don't have it on me.
  • OK found one blog.sqlauthority.com/2017/01/09/…