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). 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).
#Sql server deadlock 256 serial
If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting. It is also possible that some feature of parallel index seek range assignment contributes to the issue. 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. 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). This type of deadlock often comes and goes without an obvious external reason. 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. Notice the green highlighted non-clustered indexes maintenance. Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course. The INSERT access the clustered index first, then the nonclustered index. The SELECT accesses the nonclustered index first, then the clustered index. an INSERT query that modifies the clustered index and then the nonclustered index.a SELECT query that uses a non-covering nonclustered index with a Key Lookup.The essential ingredients for this deadlock pattern are: On the face of it, this looks like a classic lookup deadlock. If someone can help me understand why I would really appreciate it.ĮventReceivers Table: Id uniqueidentifier no 16ĮventReceivers_B圜ontextCollectionId nonclustered located on PRIMARY SiteId, ContextCollectionIdĮventReceivers_B圜ontextObjectId nonclustered located on PRIMARY SiteId, ContextObjectIdĮventReceivers_ById nonclustered, unique located on PRIMARY SiteId, IdĮventReceivers_ByTarget clustered, unique located on PRIMARY SiteId, WebId, HostId, HostType, Type, ContextCollectionId, ContextObjectId, ContextId, ContextType, ContextEventType, SequenceNumber, Assembly, ClassĮventReceivers_IdUnique nonclustered, unique, unique key located on PRIMARY Id 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.Įxecution Plan can be found on my SkyDrive SPID 356 wants an IX lock on SPID 690 resource but cannot obtain it because SPID 356 is being blocked by SPID 690 Execution ID 0 S lock SPID 690 SELECT Execution ID 0 has S lock on a page belonging to the same non clustered index SPID 356 INSERT has an IX lock on a page belonging to the non-clustered index Therefore, 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. I know this is not a solution but more a temporary modification to help with troubleshooting. Therefore, I decided to try and prevent these SELECT statements from going parallel. Since this instance is shared amongst many other databases/applications, this setting cannot be disabled. However, I have read, on the MSFT SP site, that they recommend setting MAXDOP Instance level configuration option to 1.
#Sql server deadlock 256 code
Because this is Microsoft code and table structures we cannot make any changes. I have attached the deadlock graph for your review. The deadlock graph depicts and three resources,ġ.) two from the SELECT (producer/consumer parallel threads), and The INSERT needs an IX lock on the SELECT resource, and the SELECT needs a S lock on the INSERT resource. I have narrowed down the resources involved, both processes are requiring locks on the non-clustered index.
We have been experiencing numerous SELECT/ INSERT deadlocks against one heavily utilized table within SP content database. This instance hosts the SharePoint 2007 databases (SP).
0 kommentar(er)
