none
parallel query worker thread was involved in a deadlock

    Question

  • Hi,

        We are getting deadlock issue in MSSQL 2012 enterprise edition. By enabling the trace we could find the MORE number messages as below: 

    "parallel query worker thread was involved in a deadlock"

    Along with this there are some other entries with dead lock information ( chain, graph) . 

    Please suggest.

    Thank you 

    Tuesday, January 21, 2014 6:20 PM

Answers

  • OP, I think that article posted by Saeid is exactly about your situation, although SQL2012 might have changed the message slightly.

    What the article says is bad news - you have done nothing wrong, yet SQL Server is messed up.  The only "fix" recommended is eliminating parallelism by using a hint "option (maxdop 1)" at the end of your query.  This should do the trick, but may run more slowly.

    You can try to reduce the odds of it happening by adding indexes.  Actually, ANY change to your plan or data model may make just enough changes to come up with another plan that does NOT get these self-deadlocks.  Any query that is more efficient, should reduce the odds of a deadlock.

    You can also try to analyze the deadlock as you would any deadlock, it may suggest another way to write the query. 

    Josh

    • Marked as answer by MSSQL.Arc Thursday, January 23, 2014 5:22 PM
    Wednesday, January 22, 2014 5:18 PM

All replies

  • Have a look at this link:

    Today's Annoyingly-Unwieldy Term: "Intra-Query Parallel Thread Deadlocks"


    sqldevelop.wordpress.com

    • Proposed as answer by JRStern Wednesday, January 22, 2014 5:12 PM
    Tuesday, January 21, 2014 6:33 PM
  • Hi 

          Thank you for the information...But i am not getting the error that you have specified. I am getting the below specified:

    " parallel query worker thread was involved in a deadlock "

    Thank you 

    Tuesday, January 21, 2014 6:51 PM
  • Hi

        TO give more information, its on 8 core processor .. and current MDOP setting is 0 (zero)

    Tuesday, January 21, 2014 7:00 PM
  • All,

          any information on this ... 

    Wednesday, January 22, 2014 4:06 AM
  • Hi,

        To give some additional information, we have this DB Server on VirtualEnvironment , with 8 core processor.

    Thank you

    Wednesday, January 22, 2014 5:04 PM
  • Could you please share DEADLOCK graph here?

    It looks like you may have missing index or inefficient query which is getting into a deadlock situation. We will not be able to help you unless provided with more info.

    Wednesday, January 22, 2014 5:07 PM
  • OP, I think that article posted by Saeid is exactly about your situation, although SQL2012 might have changed the message slightly.

    What the article says is bad news - you have done nothing wrong, yet SQL Server is messed up.  The only "fix" recommended is eliminating parallelism by using a hint "option (maxdop 1)" at the end of your query.  This should do the trick, but may run more slowly.

    You can try to reduce the odds of it happening by adding indexes.  Actually, ANY change to your plan or data model may make just enough changes to come up with another plan that does NOT get these self-deadlocks.  Any query that is more efficient, should reduce the odds of a deadlock.

    You can also try to analyze the deadlock as you would any deadlock, it may suggest another way to write the query. 

    Josh

    • Marked as answer by MSSQL.Arc Thursday, January 23, 2014 5:22 PM
    Wednesday, January 22, 2014 5:18 PM