SQL Server deadlocks

[sbass1]

Hi,

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 ------->

NotBackDated is "Update or Insert"
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

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:

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

[chulett]

Got no clue how SQL Server does locking, so totally guessing here but... array size? Have you tried setting all links to 1?

[sbass1]

Got no clue how SQL Server does locking, so totally guessing here but... array size? Have you tried setting all links to 1?

Darn, you're just too fast for me Craig

Yep, I was just about to post that I've solved my own issue. I changed Array Size from 1 to 1000 and Transaction Size from 0 to 1. I think Transaction Size is what did it.

From the help:

Transaction Size (default=0)

Specifies the number of rows written before a commit is executed in the database. A value of 0 causes all rows in the job to be written as a single transaction.

Array Size (default=1)

Specifies the number of rows to be transferred in one call between DataStage and the database before they are written. Enter a positive integer to indicate how often the database management system performs writes to the database. The default value is 1; that is, each row is written in a separate statement.

Larger numbers use more memory on the client to cache the rows. This minimizes server round trips and maximizes performance by executing fewer statements. If this number is too large, the client may run out of memory

Thanks for the help.

Scott

[chulett]

Sounds like you already had Array Size at 1 if you stuck with the default, so changing the Transaction Size would be what fixed it. The only "problem" with that is each row is committed as it hits the database, so you take a large performance hit and have no rollback capability.

On the plus side, though, no deadlocks!