SQL Server deadlocks
Posted: Wed Jul 08, 2009 10:28 pm
Hi,
This post is related to viewtopic.php?p=332965
So now my job looks like (excerpt):
NotBackDated is "Update or Insert"
BackDatedBalance is "Update Existing Only"
BackDatedOpted is "User-generated SQL"
That user-generated SQL is:
This is now causing deadlocks in SQL Server.
Searching the forums on "deadlock", I found this post:
viewtopic.php?t=127572&highlight=deadlock
Quoting Ray from this post:
(emphasis added)
I've tried setting Transaction Isolation to Committed, Uncommitted, Repeatable, Serializable, all without success.
So, I need to "design my job so it doesn't self-deadlock tables".
Any advice on how to do so in this scenario?
Thanks,
Scott
This post is related to viewtopic.php?p=332965
So now my job looks like (excerpt):
Code: Select all
NotBackDated --------->
BackDatedBalance -----> TargetTable (all three links, same DRS stage)
BackDatedOpted ------->
BackDatedBalance is "Update Existing Only"
BackDatedOpted is "User-generated SQL"
That user-generated SQL is:
Code: Select all
UPDATE Source.Account
SET OptedInOut=?
FROM Source.Account S, GL.DailyCalendar DC
WHERE AccountMonthlySK=? AND convert(varchar,DC.Date,112)>=? AND S.EffectiveDateSK=DC.SK
Searching the forums on "deadlock", I found this post:
viewtopic.php?t=127572&highlight=deadlock
Quoting Ray from this post:
So, really, the best solution is prevention of deadlocks in the first place - don't design jobs that self-deadlock tables, and schedule processing so that competing processes don't cause deadlocks. Standard database processing stuff.
(emphasis added)
I've tried setting Transaction Isolation to Committed, Uncommitted, Repeatable, Serializable, all without success.
So, I need to "design my job so it doesn't self-deadlock tables".
Any advice on how to do so in this scenario?
Thanks,
Scott