TRANSACTION ISOLATION LEVEL on Talend

[SDHON]

Hello,

We are using TOS 7.3 connected to ssms.

Randomly, connection reset error appears in few of the Talend Jobs launched via JOE (JobScheduler Object Editor).We tried to reschedule the jobs to avoid parallel launch as much as possible. Massive data purge did reduce the job execution time and frequency of the issue. However, still getting the error on and off now. 

One potential avenue, given we do not have transactional operational on these tables is to set our transactions to 'ReadUncommitted' isolation level to prevent any blocks/locks..On database level, all is already set to ReadUncommited. However, the isolation level is shown as ReadCommited - while the Talend jobs are running, another script (uncommited query from sys.dm_exec_sessions) runs in parallel at different interval to extract and insert in a log table which gives details on all current running jobs (host, program, login, datetime, transaction level, status etc. -- see attached snapshot. 

Is there a way to globally set/force TRANSACTION ISOLATION LEVEL on Talend itself please?

Many thanks.

[Anonymous]

There isn't a way to do this out of the box, but you may be able to build a way into your project.

[SDHON]

Hello rhall, thank you for your response.

"build a way into your project" means set transaction isolation level on each and every job/subjob (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) or there is a global way which is more direct and simpler ? Could you guide please ?

Many thanks for your help.

[Anonymous]

How are you doing this at the moment in the job/s that you have this working in? There are many ways in which functionality can be built into your projects as Talend gives you the power to add your functionality with Routines, component creation, tJava, tJavaFlex, Context variables, etc. I am presuming that you are adding this to your queries, but if you could give me an idea of what you are doing now, I am sure I can point you in an easy way to extend this.

[SDHON]

I have not added anything for the moment which set this on talend level. Only the configuration on database is set to uncommited.

I do make use of routines, context variables on TOS.

Generally, my project consists of various main which has various sub-jobs.. An example of what a particular main can be doing (as attached):

1. extract data from source (file mode FTP transfers or direct connections)
2. integrating data file to STG/ODS tables
3. ODS to DWH tables
4. script for cube refresh

Step 2 or 3 is where the connection reset happen even if we are working on the same server/database and this is followed by a connection closed as shown below:

Of course in each sub-job I use new connection and make sure it is closed at the end of each sub-job - attached the mssql connection.

Generally, the jobs run fine but I do have those connection reset which I have no idea why and how to solve. I am assuming it could be the isolation level which Talend is overwriting someway as readcommited creating blocks/locks maybe...

Is there anything which you need more from me which could help you to better guide me?

Thank you for your assistance.

[Anonymous]

OK, looking at your job I can see a few areas which *might* be exacerbating your issues here. I see you have a connection component at the beginning (after an SP component which I assume has its own connection) and one at the end. You are then calling child jobs chained together. I assume that you are querying and updating/inserting/deleting in those job. You say the same connection is not being used by everything there? If that is the case, why have the connection in the main job? It doesn't look like it is doing anything. Have you tried removing the connection components and initiating a connection for each DB component being used? It sounds inefficient, but it can actually make life a bit easier. Especially for debugging. For example, if you are querying your data with a component with its own connection and it does the job every time, you know that query is absolutely fine. However, if you are initiating a connection for every DB component and you find that the same component fails, you know where you can start looking for issues. You may also see issues caused by querying in a subjob where you are also writing to the table that you are querying from. This can be a big problem and *may* be a cause of your issue here. A nice workaround to this is to query your data in a subjob just prior to your update/insert subjob, store the data in a tHash component (or similar), then use the tHash in your subjob where the data is being updated/inserted. I have experienced that issue before and it made a massive difference.

It is very difficult to come up with a perfect solution when you are not sat in front of the problem. But the above is how I would go about trying to identify and mitigate for it. I hope that helps.

[SDHON]

Thank you for your help.

I will target a job which had this error to try the workaround and optimization you suggested.

Testing might take time as this issue happens randomly. Hoping for the best 🤞..

Btw, could you tell me the way/s to set transaction isolation level in Talend please? Many thanks rhall

[Anonymous]

I am afraid I do not have access to a MS Sql Server instance to try this. But taking a look here....

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms173763(v=sql.105)

....it appears that this needs to be part of your query. You may need to do this using a tDBRow component.

[SDHON]

Ok Thank you. This is how I thought, which is not the simplest way as it is set individually in each job.

I was looking for an equivalent but at a global higher level on TOS if possible.

[SDHON]

Hello,

One additional info please.

For the transaction isolation level, can it be configured in an SQL logon parameter, such as the auto commit parameters?

Thank you in advance.