Posted by Erland Sommarskog on 06/18/05 01:10

(jtwright@tycovalves.com) writes:
> I've got a view that creates a parent child relationship, this view is
> used in Analysis Services to create a dimension in a datastore. This
> query tends to deadlock after about 10 days of running smoothly. Only
> way to fix it is to reboot the box, I can recycle the services for a
> quick fix but that usually only works for the next 1-2 times I call the
> view.
>
> This view is used to create a breakdown of the bill-to locations from
> Continent-Global Region-Country-Sub Region-State/Province- City-Zip
> Code
>
> Yes, I know that sounds crazy, but it was a requirement.
>
> So why would I get a deadlock on a SELECT Query? Is there a way to set
> the Isolation level to Repeatable Read for a view?

You can always issue SET TRANSACTION ISOALATION LEVEL prior to running
the SELECT. Not sure that it would help though.

Indeed a deadlock on a single SELECT sounds weird. I know there are
cases where parallelism can lead to deadlock within a single query,
but that would be INSERT/UPDATE/DELETE.

But how do you know that is not interacting with any other query? Have
you enabled trace flag 1204 and checked the error log?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]