Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This instance hosts the SharePoint 2007 databases (SP). We have been experiencing numerous SELECT/INSERT deadlocks against one heavily utilized table within SP content database. I have narrowed down the resources involved, both processes are requiring locks on the non-clustered index.
The INSERT needs an IX lock on the SELECT resource and the SELECT needs a S lock on the INSERT resource. The deadlock graph depicts and three resources, 1.) two from the SELECT (producer/consumer parallel threads), and 2.) the INSERT.
I have attached the deadlock graph for your review. Because this is Microsoft code and table structures we cannot make any changes.
However, I have read, on the MSFT SP site, that they recommend setting MAXDOP Instance level configuration option to 1. Since this instance is shared amongst many other databases/applications this setting cannot be disabled.


Therefore, I decided to try and prevent these SELECT statements from going parallel. I know this is not a solution but more a temporary modification to help with troubleshooting. Therfore, I increased the “Cost Threshold for Parallelism” from our standard 25 to 40 upon doing so, even though the workload has not changed (SELECT/INSERT occurring frequently) the deadlocks have disappeared. My question is why?

SPID 356 INSERT has an IX lock on a page belonging to the non-clustered index
SPID 690 SELECT Execution ID 0 has S lock on a page belonging to the same non clustered index

Now

SPID 356 wants an IX lock on SPID 690 resource but cannot ontain it because SPID 356 is being blocked by SPID 690 Execution ID 0 S lock
SPID 690 Execution ID 1 wants a S lock on SPID 356 resource but cannot obtain it because SPID 690 Execution ID 1 is being blocked by SPID 356 and now we have our deadlock.

Execution Plan can be found on my SkyDrive

Full Deadlock Details can be found here

If someone can help me understand why I would really appreciate it.

EventReceivers Table.
Id uniqueidentifier no 16
Name nvarchar no 512
SiteId uniqueidentifier no 16
WebId uniqueidentifier no 16
HostId uniqueidentifier no 16
HostType int no 4
ItemId int no 4
DirName nvarchar no 512
LeafName nvarchar no 256
Type int no 4
SequenceNumber int no 4
Assembly nvarchar no 512
Class nvarchar no 512
Data nvarchar no 512
Filter nvarchar no 512
SourceId tContentTypeId no 512
SourceType int no 4
Credential int no 4
ContextType varbinary no 16
ContextEventType varbinary no 16
ContextId varbinary no 16
ContextObjectId varbinary no 16
ContextCollectionId varbinary no 16

index_name index_description index_keys
EventReceivers_ByContextCollectionId nonclustered located on PRIMARY SiteId, ContextCollectionId
EventReceivers_ByContextObjectId nonclustered located on PRIMARY SiteId, ContextObjectId
EventReceivers_ById nonclustered, unique located on PRIMARY SiteId, Id
EventReceivers_ByTarget clustered, unique located on PRIMARY SiteId, WebId, HostId, HostType, Type, ContextCollectionId, ContextObjectId, ContextId, ContextType, ContextEventType, SequenceNumber, Assembly, Class
EventReceivers_IdUnique nonclustered, unique, unique key located on PRIMARY Id

share|improve this question
2  
What do proc_InsertEventReceiver and proc_InsertContextEventReceiver do that we can't see in the XDL? Also to reduce parallelism why not just impact these statement directly (using MAXDOP 1) instead of futzing with server-wide settings? –  Aaron Bertrand Nov 26 '12 at 23:48
1  
I'm curious what your sever wide MAXDOP setting is and how many processors (logical) you have. SharePoint really does work better and prefer to be on a server with a MAXDOP server wide of 1.. I don't like it, but that is the way they developed it. Can you post the actual execution plans? All I see at that link is the .xdl (deadlock graph) –  Mike Walsh Nov 27 '12 at 0:00
    
Hello Gentlemen I really appreciate you taking the time, out of your busy schedule to respond. I will post both the procedures and the execution plans for your review on the SkyDrive site. I had thought about modifying the code to include the query option MAXDOP (1), however, doing so will void our support with Microsoft. The physical server is a ProLiant DL580 G4 MAXDOP setting is 4 with a total of 8 physical processors and H/T is disabled. –  SQLJarHead Nov 27 '12 at 13:58
    
Hello Gentlemen, I have created a zip package with all the details on the SkyDrive. I modified the body of the original post to include the URL for the package. Please, do not tell me what the problem is, just provide guidance and have me work for it. NOTE: I am not able to make any code changes or DDL modifications to the underlying schema. –  SQLJarHead Nov 27 '12 at 22:01
1  
So, you can't change the code and you can't change the schema, what other solutions do you expect us to come up with? If you are worried about voiding Microsoft support, then that implies that you have Microsoft support, in which case - given all the restrictions you've told us that you can't do - have you considered opening up a support ticket with Microsoft? –  Aaron Bertrand Nov 28 '12 at 3:08

2 Answers 2

On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:

  • a SELECT query that uses a non-covering nonclustered index with a Key Lookup
  • an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index. The INSERT access the clustered index first, then the nonclustered index. Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

SELECT query

...and the INSERT query is:

INSERT query

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details. This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape). Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement. It is also possible that some feature of parallel index seek range assignment contributes to the issue. If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told). Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it). If the change in cost threshold for parallelism fixes the issue for the moment, this is good. Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree. I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.

share|improve this answer
1  
Hello Mr. White, Thank you very much for this detailed analysis. I agree with the solution on covering the query for this particular deadlock scenario, matter of fact I have used such solution with other similar problems. Moreover, I have attempted to test using a covering index by including the columns listed in the SELECT statement (as INCLUDED columns) that are not part of the clustered key. (1/2) –  SQLJarHead Nov 29 '12 at 15:47
1  
Upon doing so, and this is also strange, that we no longer experienced the deadlock on this particular non-clustered index rather the optimizer decided to utilize one of the other non-clustered index, for the same exact set of queries, which attributed to additional deadlocks (INSERT/SELECT). I am going to trace further to capture the serial plans. (2/2) –  SQLJarHead Nov 29 '12 at 15:48

If this was a classic lookup deadlock, the resource list will include both the Clustered Index and Non-Clustered Index. Typically the SELECT will hold a SHARED lock on the NC index and wait for a SHARED lock on the CI, meanwhile the INSERT will acquire an EXCLUSIVE lock on the CI and wait for an EXCLUSIVE lock on the NC. The resource list in the deadlock xml will list both these objects in this case.

Since the deadlock graph only involves the NC Index we can rule out that option.

Also, If this was a dead lock due to Nested Loop Join with UNORDERED PREFETCH, the execution plan will tell us whether UNORDERED PREFETCH algorithm is used, which is again not the case here (see update below).

That leaves us to assume that this is a deadlock due to the Parallel Plan.

The deadlock graph is not rendered properly, but if you look at the Deadlock XML, you can see that two threads from the SELECT statement (SPID 690) are involved in the deadlock . The consumer thread is holding a SHARED lock on PAGE 1219645 and waiting on the producer on port801f8ed0 (e_waitPipeGetRow). The producer thread is waiting for a shared lock on PAGE 1155940.

The INSERT statement is holding an IX lock on PAGE 1155940 and waiting for an IX lock on PAGE 1219645, resulting in a deadlock.

I believe that a deadlock will be averted when using a serial plan for the SELECT statement since at no point it will require SHARED lock on more than one page. I also think that the serial plan will be almost same as the parallel plan (sans the parallelism operator).

[UPDATED based on Paul's comment]

Apparently the plan is using an OPTIMIZED Nested Loop algorithm

That explains why the SHARED locks are held until the end of the statement. REPEATABLE READ combined with parallel plan is more vulnerable to deadlock than a serial plan because the parallel plan might acquire and keep locks from different ranges of an index whereas the serial plan acquires locks in a more sequential manner.

share|improve this answer
    
Two things: (1) How can you tell the deadlock graph only involves the nonclustered index? (2) The main cause of the deadlock is surely that shared locks are held to the end of the statement. Ordinary read committed would release them much earlier (hence my link to Craig's post). –  Paul White Dec 5 '12 at 10:07
    
Agreed. If this deadlock was related to an actual LOOKUP the wait resource, for the SELECT, would have referenced the clustered index. I was able to rule that out by displaying the page header for each wait resource (SPID 690 Wait Resource = PAGE: 1155940 | SPID 356 Wait Resource = PAGE 1219645) via DBCC PAGE and both were on index ID 5 (IndexID 5 = EventReceivers_ByContextObjectId) which points to the NC index on the specified table (EventReceivers). –  SQLJarHead Dec 6 '12 at 13:17
    
Gentlemen, Thanks again for taking the time to help analyze this interesting issue. Couple of questions: 1.) Roji points out that the parallel SPID is requesting more than one page. I don’t see that in any of the execution plans. Looking at the number for rows, for the INDEX SEEK operator, only one thread out of the two producers are processing any rows. How did you determine that it was requesting more than one page? (1/2) –  SQLJarHead Dec 6 '12 at 13:38
    
2.) Will an OPTIMIZED Nested Loop algorithm always set the isolation level to REAPTABLE READ? I have checked the execution plans XML output and I only see read committed for the SPIDs connection. I am making an assumption this is only invoked at the plan operator level only. (2/2) –  SQLJarHead Dec 6 '12 at 13:38
    
The locking behavior of OPTIMIZED Nested Loops is comparable to REPEATABLE READ ( keeps the locks till the end of the statement), but it does not explicity set the isolation level of the transaction to REPEATABLE READ. I think that answers your question one too. It is not that the parallel threads are requesting more than one page at a time, but one parallel thread is holding a lock on one page and another thread is waiting for a lock on another page –  Roji P Thomas Dec 8 '12 at 1:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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