DeadLock Issue with T Connector Stage

[manu.dwhds]

Dear All,

I am facing dead lock issue with tconnector stage with update (Tpump) ,the details are as follows,

My design:

SeqFilestage---->XFm----->Tconnector(Upsert)

this is current prodution job design with dead lock issue after that i have splited into 2 jobs
First job will update the existing tables successfully then only second job will trigger to insert the data ,i assume the dead lock issue never happend in this scnario but still we are facing the same.

After this we have changed node configuration 4 node to single node its working without dead lock issue .

Kindly suggest the ways to prevent dead lock issue with Upsert logic in single job design with 4 node configuration .

Thanks in advance

[chulett]

How are you partitioning for the target? Do an exact search here for "deadlock" to find discussions of the cause and solution for this issue.

[Manfred Hagedorn]

Hello,
I made the experience, that it always comes to deadlocks with "upsert", when using mutiple nodes and having duplicates records (with respect to the defined keys).
So i think, that it can happend both nodes try to update the same record.
Manfred

[jwiles]

And this is the reason behind ensuring that your data is correctly partitioned for your update/primary key, so that duplicates are not being processed by different nodes.

I worked with a client using Teradata a year or two ago, where the developers were also experiencing deadlocks and extremely slow updates. The data was partitioned and sorted on a surrogate key rather than the actual key (due to a preceding join stage which used the surrogate). After talking with one of the TD folks, the data was repartitioned/resorted on the actual key after the join (the only change to the job), the deadlocks disappeared and the update ran in seconds rather than 1+ hours.

Regards,