Why are these two sql statements locked? (Deadlock schedule + details included)

Question

Why are these two sql statements locked? (Deadlock schedule + details included)

I have the following deadlock graph that describes two sql statements that are mutually locked to each other. I'm just not sure how to analyze this problem, and then fix my sql code so that this does not happen.

What does the code do?

I am reading in several files (for example, say 3, for this example) at the same time. Each file contains different data, but the same data type. Then I insert the data into the LogEntries table, and then (if necessary) I insert or delete something from the ConnectedClients table.

Here is 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 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 its own instance of UnitOfWork (which means that it has its own database connection, transaction context and repository). Therefore, I assume that this means that at the same time, all three connections to db occur simultaneously.

Finally, it uses the Entity Framework as a repository, but please don't let it stop thinking about this issue.

Using a profiling tool, Isolation Level is Serializable . I also tried ReadCommited and ReadUncommited , but they both mistakenly: -

ReadCommited : same as above. Dead end.
ReadUncommited : different error. EF exception, which states that he expected some result, but received nothing. I assume this is a LogEntryId Identity ( scope_identity ) scope_identity , which is expected but will not be received due to a dirty read.

Please, help!

PS. This is Sql Server 2008, by the way.

Update # 2

After reading Remus Rusanu's updated answer, I felt like I could try and provide a little more information to see if anyone else could help.

EF chart

alt text http://img691.imageshack.us/img691/600/deadlockefmodel.png

Now, Remus is offering (and mind you, he says he is not familiar with EF) ...

The last piece of the puzzle, the inexplicable lock on the left, the node has on PK_ConnectedClients, I will assume that from the implementation of EF InsertOrUpdate. It is likely to search first, but because of FK, the relationship declared between ConnectedClients and LogEntries is searched on PK_ConnectedClients, therefore acquiring a serializable lock.

Interesting. I'm not sure why the left node has a lock on PK_ConnectedClients , as suggested above. Ok, let's check the code for this method ....

 public void InsertOrUpdate(LogEntry logEntry) { LoggingService.Debug("About to InsertOrUpdate a logEntry"); logEntry.ThrowIfArgumentIsNull("logEntry"); if (logEntry.LogEntryId <= 0) { LoggingService.Debug("Current logEntry instance doesn't have an Id. Instance object will be 'AddObject'."); Context.LogEntries.AddObject(logEntry); } else { LoggingService.Debug("Current logEntry instance has an Id. Instance object will be 'Attached'."); Context.LogEntries.Attach(logEntry); } }

Hm. this is a simple AddObject (aka. Insert) or Attach (aka. Update). No links. The Sql code also does not indicate any material to search.

Ok, then ... I have two more methods ... maybe they do some searching?

In ConnectedClientRepository ...

 public void Insert(ConnectedClient connectedClient) { connectedClient.ThrowIfArgumentIsNull("connectedClient"); Context.ConnectedClients.AddObject(connectedClient); }

Nope → also basic, as.

Last Lucky Way? Wow .. now this is interesting ....

 public void Delete(string clientName, string clientIpAndPort) { clientName.ThrowIfArgumentIsNullOrEmpty("clientName"); clientIpAndPort.ThrowIfArgumentIsNullOrEmpty("clientIpAndPort"); // First we need to attach this object to the object manager. var existingConnectedClient = (from x in GetConnectedClients() where x.LogEntry.ClientName == clientName.Trim() && x.LogEntry.ClientIpAndPort == clientIpAndPort.Trim() && x.LogEntry.EventTypeId == (byte)EventType.NewConnection select x) .Take(1) .SingleOrDefault(); if (existingConnectedClient != null) { Context.ConnectedClients.DeleteObject(existingConnectedClient); } }

So, looking above, I take an instance of the record I want to delete .. and if it exists, then delete it.

So, if I comment on this method call, in my original logical path to the top of this SO-mail ... what happens?

works. WOWZ.

It also works as Serializable or Read Commited - both work if I don't call the Delete method.

So why will this delete method get a lock? . Is there a blockage and some kind of deadlock due to the choice (with Serializable )?

With read committed , is it possible that I have 3 delete calls performed simultaneously.

1st captures a data instance.
2nd (and 3rd) captures another instance of the same data.
Now, 1st removal. well.
2nd deletes .. but the line is gone .. so I get this strange error about the affected number of lines (0). <== null items removed.

Possible? If so .. uh ... how can I fix this? Is this a classic race condition case? Is there any way to prevent this?

Update

Fixed links to images.
Link to the source XML deadlock file. Here is the same link .
Added database table schema.
Both tables added.

+4

.net sql-server unit-of-work deadlock transactions

Pure.Krome Mar 20 '10 at 14:00

source share

1 answer

Remus Rusanu · Accepted Answer · 2010-03-21T01:15:14+0000

The left side of the node holds the RangeS-U lock on PK_CustomerRecords and wants to lock the RangeS-U on i1 (I assume this is an index on LogEntries ). The right side of the node has a RangeS-U lock on i1 and wants RangeI-N on PK_CustomerRecords .

Apparently, a deadlock occurs between _logEntriesRepository.InsertOrUpdate (left node) and _connectedClientRepository.Insert (right node). Unaware of the type of EF declared relationship, I cannot comment on why the left side of the node has a lock on PK_CustomerRecords at the time of inserting LogEntry . I suspect that this is caused by either an ORM type behavior called by EF, like a search for a “preloaded” member, or it may be caused by a higher level of TransactionScope that surrounds the area in the deferred code.

As others have said, it is necessary to send the database schema to a standstill, because the access path (used indexes) is critical. See My article , Block Read-Write, for a more detailed discussion of implicating indexes in dead ends.

My first recommendation would be to make the transaction scope read committed . The default serializable TransactionScopes level is almost never needed in practice, it is a high-performance whistle, and in this particular case, the advertisement causes a lot of unnecessary noise to investigate deadlocks, as a result of which range locks enter the equation, which complicates everything. Please publish the deadlock information that occurs when reading.

Also, do not place the deadlock graph image. The picture says a thousand words are not correct here, publish the original dead-end XML: it has a lot of information that is not visible in pretty pictures.

Update

From the stub XML, I see that the left node does insert [dbo].[LogEntries]([GameFileId], [CreatedOn], [EventTypeId], [Message], [Code], [Violation], [ClientName], [ClientGuid], [ClientIpAndPort]) values (@0, @1, @2, null, null, null, @3, @4, @5) ( <executionStack><frame> element). But more importantly, I see the object behind the mystical index "i1": objectname="AWing.sys.fulltext_index_docidstatus_1755869322" indexname="i1" . Thus, a deadlock occurs in the full-text index .

So a complete explanation of the dead end:

right node is in _connectedClientRepository.Insert, it needs range insert lock for PK_ConnectedClients. It has a RangeS-U lock in the full-text index i1 from the previously executed _logEntryRepository.InsertOrUpdate.
The left node is located in _logEntryRepository.InsertOrUpdate, in the INSERT statement inside the package, and it needs a RangeS-U lock at full-text index i1. It has a RangeS-S lock on PK_ConnectedClients, which blocks the right node, and this is not explained by anything in the XML plot.

The last part of the puzzle, the inexplicable castle, the remaining node, has on PK_ConnectedClients, I will assume that this is an implementation of EF in InsertOrUpdate. He probably does a search first, and because of the FK relationship declared between ConnectedClients and LogEntries, he searches on PK_ConnectedClients, therefore, acquires a serializable lock.

The main culprit here is the transaction isolation level (Serializable) and the EF behavior in InsertOrUpdate. I cannot give advice on the behavior of EF, but the serializable level is probably overpriced. Which returns us to the error that you get by the read level, which, unfortunately, is again an EF error, which I can not comment on.

Why are these two sql statements locked? (Deadlock schedule + details included)

Main Deadlock Schedule

Left side details

Right side details

XML file with six stub files

Table layout

LogEntries Table Elements

Connected Clients Information

Update # 2

EF chart

Update

More articles: