How can a read-only query cause a deadlock? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How can a read-only query cause a deadlock?

Author

Topic

lebedev
Posting Yak Master

126 Posts

Posted - 2009-05-20 : 11:33:20

SQL Server threw the following exception while executing a SELECT-query:
"Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Is this because the query joins multiple tables and requests shared locks on them in sequence somehow causing a deadlock with another query?

Should I use query hints to avoid the deadlock or should I retry running the query? What's the recommended approach?

Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 11:40:23

you should find out which transaction was causing the deadlock. You can use hints to get round the problem but that isn't good practice and is just hiding from the real issue.

Presumably some other process was updating or inserting into the table that you were reading from.

Ho many processes running on your database?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-05-20 : 11:42:46

Was it part of a transaction involving some kind of write operation (ie. Insert, delete, update)? If so, I could see it. But I can't see how a select on its own could cause a deadlock. But it could force another query to wait because unlike in Oracle, readers (ie. select) can block writers.

I believe a solution is to go to Snapshot Isolation mode (as long as you are 2005+), in which case I'd be curious how it helps you:

[url]http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx[/url]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 12:02:56

I presume that the select failed because the table was being updated or inserted into by some other process. The select then wouldn't be able to acquire a lock until that transaction / process had stopped doing it's thing.

There's probably a process that's doing something extremely inefficiently with the update or insert.

If that's the case then going to Snapshot Isolation mode is still just ignoring the warning signs of the other problem.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2009-05-20 : 12:20:36

quote:
Originally posted by Transact Charlie

I presume that the select failed because the table was being updated or inserted into by some other process. The select then wouldn't be able to acquire a lock until that transaction / process had stopped doing it's thing.

Correct me if I am wrong, but with Snapshot Isolation mode, the Select wouldn't even need to acquire the lock?

quote:

There's probably a process that's doing something extremely inefficiently with the update or insert.

If that's the case then going to Snapshot Isolation mode is still just ignoring the warning signs of the other problem.

I agree that Snapshot Isolation would not improve an inefficientcy.

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 12:33:11

you could use a dirty read hint:

SELECT x.y, x.z from dbo.table x WITH (NOLOCK)

That will pretty much prevent the possibility of deadlocking with a SELECT, unless you are joining to a view or table-valued function with some odd goings-on under the hood.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-20 : 14:19:20

It would be much better to use SNAPSHOT isolation than to use a NOLOCK hint.

There are many data consistency problems that can be caused by using a NOLOCK hint, including returning duplicate rows and failing to return rows that actually exist.

CODO ERGO SUM

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-05-20 : 15:06:58

I won't disagree with you, but the NOLOCK hint should be an easy way to avoid deadlocks. snapshot isolation has its own complications in a lengthy process.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-20 : 15:31:02

quote:
Originally posted by jholovacs
[I won't disagree with you, but the NOLOCK hint should be an easy way to avoid deadlocks. snapshot isolation has its own complications in a lengthy process...

If having correct data is important, then you should avoid using NOLOCK. There was nothing posted by the OP that indicated that the deadlocked process or the deadlocking process was a lengthy one.

Other than the overhead of using snapshot isolation, what it the complication with SNAPSHOT isolation? NOLOCK is a very radical solution when a good alternative is available.

CODO ERGO SUM

lebedev
Posting Yak Master

126 Posts

Posted - 2009-05-20 : 17:25:54

The query is a long-running report, which can run for several minutes.

Data consistency is not as important for this particular query, but is extremely important for the application in general.

I am using SQL Server 2008 with encryption enabled.

After looking a little closer, the exception is thrown after the query execution, but while the result set was being fetched.

Does this help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 00:33:27

How many records are returned?
What is your command timeout set to?

E 12°55'05.63"
N 56°04'39.26"

lebedev
Posting Yak Master

126 Posts

Posted - 2009-05-26 : 18:58:06

A few dozen records are returned.

Where can I check what the command timeout set to?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-26 : 19:06:35

Command timeout is set by the applicaiton, not by SQL Server.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources