Why are these two sql statements deadlocking? (Deadlock graph + details included).
- by Pure.Krome
Hi folks,
I've got the following deadlock graph that describes two sql statements that are deadlocking each other. I'm just not sure how to analyse this and then fix up my sql code to prevent this from happening.
Main deadlock graph
Click here for a bigger image.
Left side, details
Click here for a bigger image.
Right side, details
Click here for a bigger image.
What is the code doing?
I'm reading in a number of files (eg. lets say 3, for this example). Each file contains different data BUT the same type of data. I then insert data into LogEntries table and then (if required) I insert or delete something from the ConnectedClients table.
Here's my sql code.
using (TransactionScope transactionScope = new TransactionScope())
{
_logEntryRepository.InsertOrUpdate(logEntry);
// Now, if this log entry was a NewConnection or an LostConnection, then we need to make sure we update the ConnectedClients.
if (logEntry.EventType == EventType.NewConnection)
{
_connectedClientRepository.Insert(new ConnectedClient { LogEntryId = logEntry.LogEntryId });
}
// A (PB) BanKick does _NOT_ register a lost connection .. so we need to make sure we handle those scenario's as a LostConnection.
if (logEntry.EventType == EventType.LostConnection ||
logEntry.EventType == EventType.BanKick)
{
_connectedClientRepository.Delete(logEntry.ClientName, logEntry.ClientIpAndPort);
}
_unitOfWork.Commit();
transactionScope.Complete();
}
Now each file has it's own UnitOfWork instance (which means it has it's own database connection, transaction and repository context). So i'm assuming this means there's 3 different connections to the db all happening at the same time.
Finally, this is using Entity Framework as the repository, but please don't let that stop you from having a think about this problem.
Using a profiling tool, the Isolation Level is Serializable. I've also tried ReadCommited and ReadUncommited, but they both error :-
ReadCommited: same as above. Deadlock.
ReadUncommited: different error. EF exception that says it expected some result back, but got nothing. I'm guessing this is the LogEntryId Identity (scope_identity) value that is expected but not retrieve because of the dirty read.
Please help!
PS. It's Sql Server 2008, btw.
