Deadlock Issues using OpenXML

  • Gurus,

    In our project we have used extensively OpenXML statements in Stored Procedures inside SQL Transaction. Oflate, we started seeing lot of deadlocks happenning in our environment. On further investigation we found thatmoving openXML statments outside transaction block and populating the data in table variable and use that inside transaction helped us ot resolve issues. Is there anything connected with OpenXML and Deadlock. We tested with around 6-8 concurrent requestsfrom fronend app (ASP.NET)

  • [Disclaimer ON] I'm not a guru. [Disclaimer OFF]

    Based on my experience OpenXML is nothing special being the root cause of a deadlock compared to any other method of dealing with data.

    Most of the time (if not always) it's the way the method is used. 😉

    In order to give a more detailed answer we'd need to know the actual query being part of the deadlock scenario. Maybe XQuery instead of OpenXML will help... It depends. 😀

    As a side note:

    A table variable might be not a good choice if the OpenXML method will return many rows ince a table variable has no statistics (usually query analyzer will assume to have only one row).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I'm not a guru either.

    It sounds like you were probably hitting a combination of issues. I suspect the transactions were holding locks open for an excessive amount of time, partly because of the OPENXML and you were hitting some other, traditional, deadlock situation during that time.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • by moving the OpenXML outside the transaction we could avoid deadlokc issues, so am wondering was it a only reason

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply