[resolved] Mysqloutput locks table

[Anonymous]

Hello
I use Mysqloutput component with Insert or update action on data followed by a mysqlcommit.
During job's execution the output table is locked (if i run an sql insert it waits the end of the job to complete).
Is there an option to not lock the table during job's execution ?
Regards

Mysql version 5.5.9
Storage engine InnoDB
Talend version 4.1.0M4

[Anonymous]

Mysql is locking your table-- not Talend-- and for a good reason
if you want to do other inserts to the table in the same session, you will need to configure your db input/outputs to use a shared connection-- use the tMysqlConncetion/tMysqlCommit components to do this.

[Anonymous]

I already use shared connection (see picture of my job).
I tried to truncate the table run the job and perform inserts during job execution it works. If i run the job a second time (table is no more empty) insert statement waits the end of the job to complete.
There is certainly a good reason but i need to perform inserts during job execution.
Thanksfor your help.

[Anonymous]

Im not sure I understand your problem. It seems like everything is working the way its supposto.
First try removing the "onComponentOK" link to your commit, and replacing it with a "OnSubJobOK" link from the "tmp_addresse" component.
InnoDB should only do row-level locking by default-- This means that if you are doing plain inserts ( Action on Data==Insert ) You will still be able to select rows that have already been committed and are not being modified by another session. When your current session commits, inserts done within it will become available.
If you'd like to do insert-commit-insert-commit, set your output component to use its own connection and change the batch size to 1
This will be really slow, but data will be available to other sessions immediately.

[Anonymous]

First try removing the "onComponentOK" link to your commit, and replacing it with a "OnSubJobOK" link from the "tmp_addresse" component.
=> Done but didn't solve the problem.
I have two data sources in the same table (an "external" handled by talend and an "internal" created by my own program).
I wan't to perform inserts from my application while Talend synchornises data from an other application.
I tried to insert/update data in mysql and perform inserts with my application there is no problem. The problem seems to come from Talend not Innodb.
I cant change batch size to 1 because my job is a transaction (many tables an one commit or rollback). I provided a simple test case to describe my problem.

[Anonymous · Accepted solution]

setting mysql tx_isolation to READ-COMMITTED solved the problem

[ShastriV]

Hi , i have the same issue but i am using tMSSQLoutput component. Does reducing batch size helps?