July 22, 2008 at 5:30 pm
I have a semi-complex table join structure and a couple of nested select statements in my where clause. The statement deadlocks sometimes. How can I rewrite this to avoid deadlocks? Here is the FROM and WHERE clause. The SELECT clause is just a list of non-derived column names.
FROM DimProgram,
StageFactProgAllocation
LEFT OUTER JOIN WorkFinancialYear
ON (StageFactProgAllocation.FinancialYearId = WorkFinancialYear.FinancialYearId)
LEFT OUTER JOIN WorkManagingOffice ON (StageFactProgAllocation.ManagingOfficeId = WorkManagingOffice.ManagingOfficeId),
WorkOutlet
LEFT OUTER JOIN LoadARIAPlus2001
ON (WorkOutlet.ARIAPlus2001ClassNbr = LoadARIAPlus2001.ARIAPlusId)
LEFT OUTER JOIN LoadSLA
ON (WorkOutlet.SLA2006 = LoadSLA.SLAId)
LEFT OUTER JOIN LoadSD
ON (WorkOutlet.SD2006 = LoadSD.SDId)
LEFT OUTER JOIN LoadLGA
ON (WorkOutlet.LGA2006 = LoadLGA.LGAId)
LEFT OUTER JOIN LoadState
ON (WorkOutlet.STE2006 = LoadState.StateId)
LEFT OUTER JOIN LoadTagOutletSEIFA
ON WorkOutlet.OutletId = LoadTagOutletSEIFA.SEIFAId
LEFT OUTER JOIN LoadSEIFAByCD
ON LoadTagOutletSEIFA.ABS0107CD = LoadSEIFAByCD.[CD2001Cd]
LEFT OUTER JOIN WorkMatchTypeLookup
ON WorkOutlet.MatchTypeId = WorkMatchTypeLookup.MatchTypeId
WHERE (StageFactProgAllocation.OutletId = WorkOutlet.OutletId
AND StageFactProgAllocation.ProgramId = DimProgram.ProgramNbr)
AND StageFactProgAllocation.TotalPlaces = 0
AND DimProgram.ProgramNm IN (
SELECT ProgramNm
FROM DimProgram a
WHERE EXISTS (
SELECT 1
FROM StageFactProgAllocation b
WHERE a.ProgramNbr = b.ProgramId)
GROUP BY ProgramNm
HAVING COUNT(*) > 1
)
AND StageFactProgAllocation.AllocatedAmt > 0
July 22, 2008 at 6:01 pm
Suggestions:
1 confirm that all of the left joins are left joins (not innner)
2 set WorkOutlet as an inner join instead of , workoutlet and handled in the where
set StageFactProgAllocation as an inner join
FROM DimProgram
INNER JOIN StageFactProgAllocation ON StageFactProgAllocation.ProgramId = DimProgram.ProgramNbr
INNER JOIN WorkOutlet ON StageFactProgAllocation.OutletId = WorkOutlet.OutletId
AND StageFactProgAllocation.ProgramID = dimProgram.ProgramNbr
*NOTE: the Inner Join connection above should also replace the IN & EXISTS in the where statement
3 Remove all of the join information from the where
WHERE StageFactProgAllocation.TotalPlaces = 0 AND StageFactProgAllocation.AllocatedAmt > 0
Just updating the dimProgram.programNm in & the where exists with an external where to use an inner join may solve the majority of your deadlocks
Note2: I think the INNER JOIN to workOutLet above should return the same resultset as the original where. However without testing...
July 23, 2008 at 12:13 am
Do you know what resources its deadlocking over? If not, you can enable either traceflag 1204 or 1222 (DBCC TRACEON(1222, -1)) and get the entire deadlock graph written into the error log.
With that you can diagnose what the deadock resources are and what statement its deadlocking with.
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 23, 2008 at 12:20 am
Thanks very much Daryl 🙂
July 23, 2008 at 12:22 am
Thanks Gail. Not sure exactly which resources it's deadlocking on but have a hunch it's something to do with the subselect. Will try refining the query and try the DBCC command if that doesn't work.
July 23, 2008 at 12:27 am
This strange thing is that unless you are running in a high isolation level or this select is inside a trigger, a select shouldn't cause a deadlock.
A deadlock requires that two connections are holding locks and are waiting for locks that the other has. Since select locks are released at the end of the statement not the end of the transaction and shared locks don't block shared locks, it's very unusual to have a select alone being half of a deadlock
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 23, 2008 at 1:00 am
Maybe this helps: use WITH (NOLOCK).
I had some locking issues, and after I started using this they never showed up again.
Example:
SELECT c.*,t.* FROM customers WITH (NOLOCK)
INNER JOIN tickets t WITH (NOLOCK) ON c.id = t.customersid
etc. etc.
Don't forget to look up pros and cons when using it (for example don't use it when you must avoid dirty reads)
July 27, 2008 at 5:50 pm
Hi All,
Daryl: I've tried removing the WHERE clause information and realised that it now seems to miss the point of the WHERE clause - that is; I'm not getting a list of ProgramNms HAVING COUNT(*) > 1.
Gail: I agree it seems odd that one transaction is causing the deadlocks. Unless a previous transaction is left open for some reason, I can guarantee that this is the only transaction hitting the database (let alone the specific tables).
R.: Thanks for that, I'll try it now.
It's hard to replicate this as (like every such issue) it doesn't happen all the time, and I've only noticed it on one particular server.
Sam
July 27, 2008 at 8:50 pm
Do you have the new query? thanks
July 28, 2008 at 3:17 am
Sam Peascod (7/27/2008)
Gail: I agree it seems odd that one transaction is causing the deadlocks. Unless a previous transaction is left open for some reason, I can guarantee that this is the only transaction hitting the database (let alone the specific tables).
Deadlocks can't occur if there's only one transaction. The requirement to get a deadlock is that two or more transactions are requesting locks that the others have (see - What is a deadlock[/url])
Have you enabled one of the traceflags? Without the deadlock graph it's very difficult to debug a deadlock since only one of the connections gets the error.
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 5:14 pm
All,
Given the problem is a bit sporadic, and I don't have easy access to testing the problem, I'm going to go with the "(NOLOCK)" solution for now. Thanks for your help. Will reply here if I get to testing this and it still doesn't work (which may be a little while).
Sam
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply