Hi,
I am running into a rather perplexing issue and am hoping someone can be of help.
We are encountering a problem that has our database locking up when a native sql query is being run at the same time a Talend based process is running. It looks like the Talend job is blocking access to a table that is being read by the native sql call and the Talend job and this in turn is backing up all other calls to the database. But before you say, it is a dba thing... the problem is only occurring when this one Talend job runs at the same time this particular native sql query runs (is a select only statement).
The Talend job is a rather straightforward one that uses a tMySQLInput component to read the data, a tMap component to map the data, and a tMySQLOutput component to load the data. Nothing complex here.
Onto my question... is anyone aware of any locking that may take place by a Talend job for tables it reads? The key word here is reads. The table that gets loaded is a basic extract table used only by the job mentioned above and the tables common between the Talend job and the native sql are used for reads only. I do not see any options in the job that indicates whether a table read should be shared or exclusive and am not sure why this job is bringing the database to a standstill.
Any input is greatly appreciated.
Regards,
Tom G.
Mysql will by default (using Myisam engine) do table-level locking. This is expected behavior. One strategy for not interfering with normal operations is to load a "staging" table with the talend job, and then quickly do a "table swap" rename operation i.e.:
ALTER TABLE REAL_TABLE RENAME TO REAL_TABLE_OLD; ALTER TABLE STAGE_TABLE RENAME TO REAL_TABLE; DROP TABLE REAL_TABLE_OLD;
Hi John,
Thanks for your suggestion. I am not sure that we can use the rename table option due to the nature of the table being read from - it is one of the more important and most used tables in the DW and swapping it may create additional problems.
Having said the above, it looks like there may have been other factors (being researched at the moment) with the issue I encountered yesterday. We performed additional tests this morning including the same ones as yesterday and are not encountering a locking condition. Curious.
I'll update this post once the issue is better understood and a solution has been found.
Thanks again.
Tom
Try to extend your query in the input component with "with ur" means "with uncommited read" that should avoid locking the table. Use for int tMysqlInput an external tMysqlConnection and configure the connection component as auto commit.
I'm facing the same issue , is there any resolution to this problem ? I'm using tdbinput connection to read from a SQL Server database table, during this process Talend applies a lock on the table , any other process reading the data faces deadlock
