Sql-server – Concurrent Update On Primary Key Column Making Deadlock

deadlocksql serversql server 2014

I have simple table T which have Col1, Col2,Col3 and Col4. columns. Col1 is primary key. Now I am running some concurrent code,

Parallel.For(0, list.Count, new ParallelOptions { MaxDegreeOfParallelism = -1 }, j =>
{
   var obj= list[j];
   // Do some work
   // UPDATE T SET Col2, Col3, Col4 WHERE Col1 =@Col1

Each value of col1 is different. But when I run this code I am getting too much deadlocks. Since Col1 is PK(Clustered Index), I am now confuse why this is happening?

In the deadlock vistim. I see Owner Mode: U

Whenever I run,

USE master
GO
DROP TABLE temp_sp_who2
GO
CREATE TABLE temp_sp_who2 ( SPID INT, Status VARCHAR(1000) NULL, Login SYSNAME NULL, HostName SYSNAME NULL, BlkBy SYSNAME NULL,DBName SYSNAME NULL,Command VARCHAR(1000) NULL, CPUTime INT NULL,DiskIO BIGINT NULL,  LastBatch VARCHAR(1000) NULL, ProgramName VARCHAR(1000) NULL,SPID2 INT , RequestId INT NULL )
GO

INSERT  INTO temp_sp_who2
EXEC sp_who2

SELECT  BlkBy ,count(*) FROM 
temp_sp_who2 WHERE CAST(BlkBy AS NVARCHAR(MAX)) <> CAST(SPID AS NVARCHAR(MAX)) 
GROUP By BlkBy
ORDER BY Count(*) DESC
GO

I see BlkBy is different with high numbers. But BlkBy is always high when app is running.

Best Answer

Without the deadlock trace, we can only guess based on symptoms.

Long-term blocking and deadlocks suggest the singleton row update query is performing a full scan rather than locating the row by the PK index. This will happen if the primary key parameter data type is different than the referenced column and results in a non-sargable expression that can't use the PK index efficiently.

Attention to detail is important in declaring parameters in app code or stored procedure to ensure the parameter definition matches the referenced column type. A common cause of mismatched types (not necessarily yours) in ADO.NET is the AddWithValue method, which infers the SQL type based on the supplied value. Strings in .NET are Unicode so the resultant parameter type of a string value is nvarchar. Comparing an nvarchar parameter with a legacy SQL collation varchar column precludes using the primary key index to locate the row, resulting in a full scan that leads to blocking and deadlocks on on active system.

Related Question