By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,142 Members | 1,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,142 IT Pros & Developers. It's quick & easy.

Deadlock in View with Select Union - Creating A/S Dimension

P: n/a
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?

Here is the view code:

CREATE View dbo.vwBillTo
as
-- US ZipCode
Select 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +
cast(IsNull(z.US_Region_wk,0) as varchar) + ')'
,z.Zipcode_WK as 'Child'
,z.ZipCode_WK as 'Child_ID'
From dbo.DIM_POSTAL_CODES_US z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f.Bill_To
Where z.US_Region_wk IS NOT NULL
Group by z.City,z.ZipCode_WK,US_Region_wk, z.State_shrt

Union
--City
Select 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) as
varchar)+')'
,z.City as 'Child'
,z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +
cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'
From dbo.DIM_POSTAL_CODES_US z
Where z.US_Region_wk IS NOT NULL
Group by z.State_Long,z.City,z.State_shrt,z.US_Region_wk

Union

-- Canada ZipCode
Select 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +
')'
,z.Zipcode_WK as 'Child'
,z.Zipcode_WK as 'Child_ID'
From dbo.DIM_POSTAL_CODES_CAN z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f.Bill_To
Group by z.Province_Long,z.ZipCode_WK, z.City, z.province_shrt

Union

--City
Select 'Parent'=z.Province_Long
,z.City as 'Child'
,z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as
'Child_ID'
From dbo.DIM_POSTAL_CODES_CAN z
inner join dbo.FACT_SALES f
on z.ZipCode_WK=f.Bill_To
Group by z.Province_Long,z.ZipCode_WK, z.City, z.province_shrt
Union

-- Canada Province
Select 'CANADA'
,Province_Long
,Province_Long
From dbo.DIM_POSTAL_CODES_CAN
Group by Province_Long

Union
-- Country
Select t.Region_NK
,c.Country_Name
,c.Country_Name
From dbo.DIM_COUNTRY c
Inner Join dbo.DIM_WORLD_REGION t
On c.Region_WK=t.Region_WK
Where c.Country_Name Is Not Null
Group by t.Region_NK, c.Country_Name

Union

-- SubRegion
Select c.Country_Name
,sr.US_Region_Name
,sr.US_Region_Name
From dbo.DIM_US_REGION sr
Inner Join dbo.DIM_COUNTRY c
On sr.Country_wk=c.Country_WK
Group by c.Country_Name, sr.US_Region_Name

Union
--Region
Select sr.US_Region_Name
,c.State_Long
,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'
From dbo.DIM_US_REGION sr
Inner Join dbo.DIM_POSTAL_CODES_US c
On sr.US_Region_WK=c.US_Region_WK
Group by sr.US_Region_Name, c.State_Long,c.US_Region_wk

Union
-- Continent
Select Null
,Region_NK
,Region_NK
From dbo.DIM_WORLD_REGION

Where Region_NK Is Not Null

Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(jt******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.