tmysqlrow - Lock wait timeout error with large set of data

One Star

tmysqlrow - Lock wait timeout error with large set of data

Hi,
I created an ETL process with a series of jobs. In one particular job (job1), I use tMysqlRow to insert/update into a warehouse table with roughly 6.5 million rows. The job fails due to integrity constraint issue in db (mysql). I have a post job that updates an audit table to capture the error but that fails due to:

Java Exception~java.sql.SQLException:Lock wait timeout exceeded; try restarting transaction
The integrity constraint issue I can fix, but it's the above error I am concerned about..

Digging around the forum, it seems a lock is not released by another process which I assume is due to job1 failure. Can anyone explain what the exact issue here? Is this something that is caused by Talend (not properly closing current connection, rollback issue, etc) or is this mysql issue? Some forums suggest to set the mysql innodb_lock_wait_timeout from default 50 secs to some higher number. But I am not convinced that this will solve the problem in the long term.
This seems to be happening with large set of data.
Here's the general config of Job1
tMysqlRow - commit level set at 10,000.
thanks
Sarah

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download