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