'Deadlock in SQL Server in simple update operation

I have a SQL command like this:

UPDATE tableName 
SET ColA = @ColA, ColB = @ColB, ColC = @ColC
WHERE ID = @ID

which is executed as the sole command in its own transaction. Multiple of these transactions are executed simultaneously from across multiple program threads (presumably regarding different IDs), and in under some unknown circumstances, I get deadlocks:

Error: Transaction (Process ID 372) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Statement: UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID

The queries are run on about fifty different installations/environments, these deadlocks only occur on a few select machines.

I have found a post on MSDN that this can be caused by a missing index, however, the ID column is the primary key. Do I have to add another index on that column, and if so, which type of index would work?

EDIT: This is the content of an xml_deadlock_report retrieved from Management -> Extended Events -> System_Health -> package0.event_file:

<deadlock>
 <victim-list>
  <victimProcess id="process3b6651c38" />
 </victim-list>
 <process-list>
  <process id="process3b6651c38" taskpriority="0" logused="0" waitresource="PAGE: 45:1:12951 " waittime="3873" ownerId="35041102" transactionname="UPDATE" lasttranstarted="2018-03-28T09:34:11.700" XDES="0x3bf35dd40" lockMode="U" schedulerid="2" kpid="6972" status="suspended" spid="306" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2018-03-28T09:34:11.700" lastbatchcompleted="2018-03-28T09:34:11.700" lastattention="1900-01-01T00:00:00.700" clientapp=".Net SqlClient Data Provider" hostname="WSERVER01" hostpid="1984" isolationlevel="read committed (2)" xactid="35041102" currentdb="45" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000b14fad3a90dbdc90c36b1078cbce60ffaeefea220000000000000000000000000000000000000000">
UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ID nvarchar(152),@ColA int, @ColB int, ColC varchar(30))UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID   </inputbuf>
  </process>
  <process id="process3b2d22188" taskpriority="0" logused="0" waitresource="PAGE: 45:1:42575 " waittime="3874" ownerId="35041102" transactionname="UPDATE" lasttranstarted="2018-03-28T09:34:11.700" XDES="0x27dd93c50" lockMode="U" schedulerid="4" kpid="7572" status="suspended" spid="306" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2018-03-28T09:34:11.700" lastbatchcompleted="2018-03-28T09:34:11.700" lastattention="1900-01-01T00:00:00.700" clientapp=".Net SqlClient Data Provider" hostname="WSERVER01" hostpid="1984" isolationlevel="read committed (2)" xactid="35041102" currentdb="45" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000b14fad3a90dbdc90c36b1078cbce60ffaeefea220000000000000000000000000000000000000000">
UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ID nvarchar(152),@ColA int, @ColB int, ColC varchar(30))UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID   </inputbuf>
  </process>
  <process id="process3b2d250c8" taskpriority="0" logused="10000" waittime="3611" schedulerid="3" kpid="1496" status="suspended" spid="317" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-03-28T09:34:11.673" lastbatchcompleted="2018-03-28T09:34:11.673" lastattention="1900-01-01T00:00:00.673" clientapp=".Net SqlClient Data Provider" hostname="WSERVER01" hostpid="1984" loginname="dbUser" isolationlevel="read committed (2)" xactid="35041090" currentdb="45" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000b14fad3a90dbdc90c36b1078cbce60ffaeefea220000000000000000000000000000000000000000">
UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ID nvarchar(152),@ColA int, @ColB int, ColC varchar(30))UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID   </inputbuf>
  </process>
  <process id="process3b2d24cf8" taskpriority="0" logused="19872" waitresource="PAGE: 45:1:42575 " waittime="3782" ownerId="35041090" transactionname="UPDATE" lasttranstarted="2018-03-28T09:34:11.673" XDES="0x3bf361d40" lockMode="U" schedulerid="3" kpid="2600" status="suspended" spid="317" sbid="0" ecid="4" priority="0" trancount="0" lastbatchstarted="2018-03-28T09:34:11.673" lastbatchcompleted="2018-03-28T09:34:11.673" lastattention="1900-01-01T00:00:00.673" clientapp=".Net SqlClient Data Provider" hostname="WSERVER01" hostpid="1984" isolationlevel="read committed (2)" xactid="35041090" currentdb="45" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000b14fad3a90dbdc90c36b1078cbce60ffaeefea220000000000000000000000000000000000000000">
UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ID nvarchar(152),@ColA int, @ColB int, ColC varchar(30))UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID   </inputbuf>
  </process>
  <process id="process3b6657498" taskpriority="0" logused="19872" waitresource="PAGE: 45:1:12951 " waittime="3780" ownerId="35041090" transactionname="UPDATE" lasttranstarted="2018-03-28T09:34:11.673" XDES="0x3acb75760" lockMode="U" schedulerid="1" kpid="2748" status="suspended" spid="317" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2018-03-28T09:34:11.673" lastbatchcompleted="2018-03-28T09:34:11.673" lastattention="1900-01-01T00:00:00.673" clientapp=".Net SqlClient Data Provider" hostname="WSERVER01" hostpid="1984" isolationlevel="read committed (2)" xactid="35041090" currentdb="45" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="66" sqlhandle="0x02000000b14fad3a90dbdc90c36b1078cbce60ffaeefea220000000000000000000000000000000000000000">
UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@ID nvarchar(152),@ColA int, @ColB int, ColC varchar(30))UPDATE tableName SET ColA=@ColA, ColB=@ColB, ColC=@ColC WHERE ID=@ID   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="12951" dbid="45" subresource="FULL" objectname="dbName.dbo.tableName" id="lock266e05c00" mode="U" associatedObjectId="72057594041663488">
   <owner-list>
    <owner id="process3b2d250c8" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process3b6651c38" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="42575" dbid="45" subresource="FULL" objectname="dbName.dbo.tableName" id="lock2668a9280" mode="U" associatedObjectId="72057594041663488">
   <owner-list>
    <owner id="process3b6657498" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process3b2d22188" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <exchangeEvent id="Pipe32a7fe700" WaitType="e_waitPipeGetRow" nodeId="2">
   <owner-list>
    <owner id="process3b6657498" />
    <owner id="process3b2d24cf8" />
   </owner-list>
   <waiter-list>
    <waiter id="process3b2d250c8" />
   </waiter-list>
  </exchangeEvent>
  <pagelock fileid="1" pageid="42575" dbid="45" subresource="FULL" objectname="dbName.dbo.tableName" id="lock2668a9280" mode="U" associatedObjectId="72057594041663488">
   <owner-list>
    <owner id="process3b2d22188" mode="U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process3b2d24cf8" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="12951" dbid="45" subresource="FULL" objectname="dbName.dbo.tableName" id="lock266e05c00" mode="U" associatedObjectId="72057594041663488">
   <owner-list>
    <owner id="process3b6651c38" mode="U" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process3b6657498" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

What can I read from this?

sql-server sql-server-2012

Solution 1:^[1]

It sounds like Lock escalation is taking place - even though you are only updating 1 row SQL will pessimistically lock the page or even the table.

One option that worth trying is to use the WITH ROWLOCK hint on your update. In theory this will apply the locking only on the affect row.

Solution 2:^[2]

After many many problems with deadlocks, I struggled to isolate them to a specific issue, but did find a way to resolve them in 99.99% of the instances we had. We had 1 table that was frequently accessed by 1000s of users. We found deadlock errors were occurring roughly once in about 100,000 UPDATE queries, but this was enough to cause significant headaches and customer complaints.

The solution: Define a simple TRY/CATCH function to try multiple times before giving up

Function ExecuteSQL_TryCatch ( DB_Object , 
    SQL , 
   MaxAtempts, 
   DelayBetweenAtempts , 
   ErrorsCaughtWrite )

  AttemptCounter = 0 
  AttemptSuccess = "no"

  While AttemptCounter < MaxAtempts 

   try {
     DB_Object.Execute SQL
     AttemptSuccess = "yes"
   } 

   catch {
    Pause ( DelayBetweenAtempts )
   }

   AttemptCounter += 1

   If AttemptSuccess = "yes" Then
     // Exit the while loop
   End If 

  Wend

End Function

//THIS IS THE QUERY

SQL = "UPDATE tblCalendarEntries SET calendarEntryDetails = 'this is a happy day' WHERE calendarEntryID = 101 "

ExecuteSQL_TryCatch ( DBCON ,  SQL ,  5 , 2 , "yes" )

This solution obviously does not fit all circumstances / errors and you may wish to qualify the nature of the error before deciding to retry, but for us this fixed everything.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	john McTighe
Solution 2

'Deadlock in SQL Server in simple update operation

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]