DB Deadlock Problem

[AJadoo1617794336]

Hi Guys,

I'm using Talend Open Studio For ESB 7.3.1 and I created a job that reads from a message queue then insert/update into a database (MS SQL Server), the same job design will be used twice simultaneously, each one will read from it's own queue but both will insert/update into the same DB.

When I executed the jobs a deadlock error messages occurred so many transactions failed.

I used tMomInput to read from the queue, tDBOutput (tMSSqlOutput) to insert/update DB tables, tXMLMapper to map.

I tried to modify the Commit every value many times and I even changed it to 1 and the deadlocks still occurs.

Is there anyway to solve this using Talend itself?

Best Regards.

[gjeremy1617088143]

Hi, it's because you try to send two high priority request to the database table, for security reason il will only take one at a time

[AJadoo1617794336]

Actually no, is this effective? does it matter?

[AJadoo1617794336]

So you think that the solution should be in the job design and the way it handles the DB, not in Talend component usage?

[Anonymous]

Hello,

You have committed your SQL output for each and every row in advanced setting of tMSSqlOutput component?

Could you post an image of your job?

Best regards

Sabrina

[AJadoo1617794336]

Hello,

Yes I committed row by row and still have a deadlock issue because the same job design is executed twice in parallel with different queues.

 Please check images below.

Any help is appreciated, Thanks.

[AJadoo1617794336]

 

[Anonymous]

Hello,

We suspect that you have not committed some of the previous transaction and its going to a lock stage. 

Could you please check the details using the commands specified in the below link?

https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transa...

https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans

In addition to that, would you mind posting your full error stack trace here?

Best regards

Sabrina

[AJadoo1617794336]

Here is the error message I got:

Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.