topic Re: DB Deadlock Problem in Talend Studio

DB Deadlock Problem

AJadoo1617794336 — Sat, 16 Nov 2024 00:04:42 GMT

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.

Re: DB Deadlock Problem

gjeremy1617088143 — Tue, 08 Jun 2021 13:09:41 GMT

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

Re: DB Deadlock Problem

AJadoo1617794336 — Tue, 08 Jun 2021 13:15:08 GMT

Actually no, is this effective? does it matter?

Re: DB Deadlock Problem

AJadoo1617794336 — Tue, 08 Jun 2021 14:22:37 GMT

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

Re: DB Deadlock Problem

Anonymous — Wed, 09 Jun 2021 07:33:10 GMT

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

Re: DB Deadlock Problem

AJadoo1617794336 — Wed, 09 Jun 2021 08:22:05 GMT

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.

Re: DB Deadlock Problem

AJadoo1617794336 — Wed, 09 Jun 2021 08:25:21 GMT

Re: DB Deadlock Problem

Anonymous — Thu, 10 Jun 2021 08:09:56 GMT

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-transaction-even-though-im

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

Re: DB Deadlock Problem

AJadoo1617794336 — Sun, 13 Jun 2021 06:53:55 GMT

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.