Post reply

Deadlock Issues using OpenXML

  • September 27, 2010 at 1:16 pm

    #147098

    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)

  • September 27, 2010 at 2:38 pm

    #1228017

    [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]

  • September 28, 2010 at 7:00 am

    #1228301

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • September 28, 2010 at 9:38 am

    #1228486

    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