0

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.

8
  • What does // Do some work involve? Is it doing some other DML under the same transaction? Will the sessions have already done anything else in this transaction before they run this update? – Andrew Sayer Mar 20 at 11:55
  • Nothing related to DB – user960567 Mar 20 at 12:01
  • Are there any other indexes on the table? Foreign keys? Triggers? – Erik Darling Mar 20 at 12:14
  • Erik no other index and fk. This is just a table with Pk. There is one column with nvarchar(max). This table is only used in this process and not any other place. – user960567 Mar 20 at 12:23
  • 2
    Add the deadlock trace to your question. Make sure the parameter data type in the app code matches the column data type. – Dan Guzman Mar 20 at 12:24
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.