July 28, 2008 at 2:25 pm
Hey all,
I'm having a hard time understanding these deadlocks ... please see the attached file. I'm not sure why these queries are deadlocking when they're simply select statements.
Any help would be much appreciated.
Thanks
July 28, 2008 at 2:38 pm
Strange. The locks are IX or SIX (intent exclusive or select with intent exclusive). A simple select should just be taking an S lock.
Are these queries part of a stored procedure? Is the referenced object a table or a view? Are there any triggers involved?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 2:48 pm
Unfortunately this is a third party vendor application and as of now I do not know much of anything to as how it works. This just happened and I'm just starting to dive into it ... I was really looking to find if anything was glaringly obvious here.
Thus far the only thing that sticks out is that one is IX and the other is SIX. Why/how is it possible that a simple select statement is attempting to use anything more than a shared lock? It would appear that both spids are attempting to obtain an exclusive lock at the same time; thus causing the deadlock.
Doesn't look like these tables are indexes for these queries and are table scanning ... perhaps a cause of why we're attempting to obtain an exclusive? I think I'm going to start there and add a couple indexes ...
July 28, 2008 at 2:52 pm
The one case where I've seen a select take an X lock is inside a trigger if selecting from the table the trigger is on.
At this point, I'd suggest haul profiler out and see if you can find where this select is coming from. Does it deadlock often?
Adam Bean (7/28/2008)
Doesn't look like these tables are indexes for these queries and are table scanning ... perhaps a cause of why we're attempting to obtain an exclusive? I think I'm going to start there and add a couple indexes ...
Lack of indexes could cause a table scan, won't cause an exclusive lock though. Exclusive lock means that data is being changed or someone forced (via a locking hint) an exclusive lock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 28, 2008 at 2:56 pm
In a month's time, it's deadlocked about 6 times total ... so not to bad.
I don't see any triggers at all in this database and there are only a few procs, none of which contain these queries. So they have to be an ad-hoc query from the application.
Oh and to answer an earlier question of yours, yes, these are tables and not views.
July 28, 2008 at 6:16 pm
I think that you need to find out what the other process in the deadlock is doing also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 29, 2008 at 4:00 am
As far as I can tell from the deadlock graph, both parts of the deadlock are selects that are taking IX or SIX locks. If I've misinterpreted the deadlock graph, please say.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 29, 2008 at 6:04 am
rbarryyoung (7/28/2008)
I think that you need to find out what the other process in the deadlock is doing also.
They are listed in the attached doc on the original post. Both are selects.
July 29, 2008 at 6:05 am
GilaMonster (7/29/2008)
As far as I can tell from the deadlock graph, both parts of the deadlock are selects that are taking IX or SIX locks. If I've misinterpreted the deadlock graph, please say.
You are correct, and that's where I'm confused. You even stated yourself that a select should not be doing this ... this is my understanding as well and I'm pretty lost on to as why selects are deadlocking as they're trying to obtain exclusives.
July 29, 2008 at 7:03 am
I think you'll have to find the source of these to get any clarity on why the locks are IX.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply