Deadlock on simple query

Question

Deadlock on simple query

Chuck Bevitt

Right there with Babe

Points: 754
More actions
June 28, 2016 at 5:25 pm

#314675

I've been getting deadlocks in a simple query like:
Select {data}
From
DataTable DT
Inner Join SetTable ST on ST.SetTableID = DT.SetTableID
Where {filter in part by flag in SetTable}
I was a little surprised because I'm more used to seeing deadlocks in updates. So I tried changing the query to add nolock on the SetTable as:
Select {data}
From
DataTable DT
Inner Join SetTable ST With(NoLock) on ST.SetTableID = DT.SetTableID
Where {filter in part by flag in SetTable}
The change had no effect. I can't use SnapShot because it's not turned on for our databases and I don't want to allow dirty reads on the DataTable in this application.
I can understand how this query could get blocked and perhaps receive an ADO.Net timeout, but how could the modified query be getting deadlocked? Does my application of NoLock do what I think it does? I assumed that it would allow reading the SetTable table regardless of what locks were on it so that the query could only be held up by locks on the DataTable, which is would wait for, but never get in a deadlock position.
Thanks,
Chuck Bevitt

Viewing 8 posts - 1 through 7 (of 7 total)

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

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

You're saying deadlock. Do you mean that the SELECT query in question is being chosen as a deadlock victim and it's transaction is being rolled back? Or do you mean that the query is blocked? They are very distinct and will require completely different approaches to deal with (although both are performance related).

"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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you post the deadlock graph?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 3

Chuck Bevitt (6/28/2016)
I've been getting deadlocks in a simple query like:
Select {data}
From
DataTable DT
Inner Join SetTable ST on ST.SetTableID = DT.SetTableID
Where {filter in part by flag in SetTable}
I was a little surprised because I'm more used to seeing deadlocks in updates. So I tried changing the query to add nolock on the SetTable as:
Select {data}
From
DataTable DT
Inner Join SetTable ST With(NoLock) on ST.SetTableID = DT.SetTableID
Where {filter in part by flag in SetTable}
The change had no effect. I can't use SnapShot because it's not turned on for our databases and I don't want to allow dirty reads on the DataTable in this application.
I can understand how this query could get blocked and perhaps receive an ADO.Net timeout, but how could the modified query be getting deadlocked? Does my application of NoLock do what I think it does? I assumed that it would allow reading the SetTable table regardless of what locks were on it so that the query could only be held up by locks on the DataTable, which is would wait for, but never get in a deadlock position.
Thanks,
Chuck Bevitt

Do you have index on that table? How many records exists? You might be doing a table scan which might be causing the deadlock. As Gail mentioned, post Deadlock Graph here.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Probably a key-lookup deadlock, table scans wouldn't cause a deadlock if it's just these two queries.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chuck Bevitt Right there with Babe Points: 754 More actions · Answer 5

Here's the deadlock graph from one of the occurrences.

I think a light is starting to come on - what I was looking at as a simple read query with a filter can involve several indexes on a single table. Since I'm reading committed, this would require some level of locks on multiple indexes - which is where the potential for a deadlock would come from. One process might have a lock on index A and trying to get a lock on index B while another process has a lock on index B and trying to get a lock on index A. I just wasn't thinking of deadlocks in terms of indexes.

Will appreciate further comments on this.

I had mentioned in my post that Snapshot wasn't an option as we don't have Snapshot turned on in our databases. As our business volume increases, we seem to be getting more issues like deadlocks and (ADO.Net) timeouts. We can't go back and overhaul 15 years of application coding all at once; however for new or updated app there might be places were dirty reads would be unacceptable but snapshot would be. Should I push our IT department to consider turning Snapshot on? I know it involves additional overhead, is there a way to predict in advance what the impact would be?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Screen shots of the graph are nearly useless, all the interesting information is in tooltips. Please either attach the deadlock graph file or paste the XML

Also the table definition and the definitions of the two indexes mentioned please.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110206 More actions · Answer 7

Chuck Bevitt (6/30/2016)
Here's the deadlock graph from one of the occurrences.
I think a light is starting to come on - what I was looking at as a simple read query with a filter can involve several indexes on a single table. Since I'm reading committed, this would require some level of locks on multiple indexes - which is where the potential for a deadlock would come from. One process might have a lock on index A and trying to get a lock on index B while another process has a lock on index B and trying to get a lock on index A. I just wasn't thinking of deadlocks in terms of indexes.
Will appreciate further comments on this.
I had mentioned in my post that Snapshot wasn't an option as we don't have Snapshot turned on in our databases. As our business volume increases, we seem to be getting more issues like deadlocks and (ADO.Net) timeouts. We can't go back and overhaul 15 years of application coding all at once; however for new or updated app there might be places were dirty reads would be unacceptable but snapshot would be. Should I push our IT department to consider turning Snapshot on? I know it involves additional overhead, is there a way to predict in advance what the impact would be?

SELECT query would only apply S(hared) locks - that's what you have on the left side of your graph.

But what is causing X(clusive) locks on the right side?

It cannot be a SELECT.

_____________
Code for TallyGenerator