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