September 27, 2010 at 1:16 pm
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
[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).
September 28, 2010 at 7:00 am
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
September 28, 2010 at 9:38 am
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