CODEWITHSUNDEEP
c#multithreadingconsole-applicationdeadlockdatabase-deadlocks
karry
karry

Reputation: 3410

Multi threaded application DeadLock issue with Database

I am working with a Multi threaded Console Application, in which each thread basically tries to obtain the TOP 1 "File" row with certain criteria met and locks it.( There is a LockID column which is populated when this happens so that the next thread picks up the next available 'unlocked' 'File' row)

We put a monitor on the SQL Server DB and each time the deadlock happens on 2 queries.

SELECT TOP 1 F.Id, F.ContentTypeId, F.ManufacturerId, F.DocumentTypeId, F.Name, F.Description, F.VersionId, F.LastChangedVersionOn, F.ReferenceCount, F.LastChangedReferencesOn, F.LastChangedImageOn, F.ImageSize, F.IsStale, F.InvalidFile, CT.Id, CT.Name, CT.MimeType, CT.IsMimeAttachment, CT.Extensions, CT.CanTrackVersions, CT.UseRemoteSource, CT.FullTextFilter, CT.ContentHandler, V.Id, V.Size, V.Hash, V.Title, DT.Id, DT.Code, DT.Ordinal, DT.Name, DT.PluralName, DT.UrlPart
FROM Docs.Files F
INNER JOIN Docs.ContentTypes CT ON CT.Id = F.ContentTypeId
LEFT JOIN Docs.Versions V ON V.Id = F.VersionId
LEFT JOIN Docs.DocumentTypes DT ON DT.Id = F.DocumentTypeId
WHERE (F.LockId IS NULL OR F.LockedOn < DATEADD(hh,-1,GETUTCDATE()))
AND F.IsStale = 1 AND F.InvalidFile = 0

AND

(@Id int)UPDATE Docs.Files
SET LastChangedImageOn = GETUTCDATE(), ImageSize = (
    SELECT DATALENGTH(FileImage)
    FROM Docs.FileImages
    WHERE FileId = @Id)
WHERE Id = @Id;
SELECT TOP 1 LastChangedImageOn FROM Docs.Files WHERE Id = @Id

The first query is run when a new thread is created and we try to obtain a new 'File' row.

The second query is run when a thread(may be a previously created one) is almost done with processing the 'File' record.Used Transactions on this query. Isolation level was "ReadCommitted". I'm pretty sure that both the queries aren't trying to access the same "FileID" because two threads would never process the same "FileID" subsequently. I'm terribly confused as to how I can diagnose this issue. What might be causing the deadlock between these 2 queries? I'd really really appreciate it if someone could guide me in the right direction. Thanks a lot in advance :)

Upvotes: 1

Views: 680

Answers (1)

Paul
Paul

Reputation: 288

Hmm... it's been a while since I did anything with SQL Server. But let's try.

You mention that "two threads would never process the same "FileID" subsequently", how can you be sure of this? Is the ID being fed from a source outside the thread?

Upvotes: 1

Related Questions