So my current web service seems to be running into a weird problem.
I have a tRestRequest --> tJavaRow ( read some data from the request ) --> tMSSqlConnection --> tMSSQLOutput (Save the data from request) --> tMSSqlLastInsertID .
I save this info from tMSSqlLastInsertedID in a context variable which is used at a later stage.
I have seen a couple of instances where requests reaching almost simultaneously to the service and the tMSSqlLastInsertID context variable gets overwritten before it gets written in other DB tables...
What is the best way to prevent the context variable being overwritten when multiple parallel requests come in?
Hmmm this is interesting. Can you make a change and test it? Replace your context variables and use the globalMap. In the past when there were issues with thread safety in the ESB, I would go to the globalMap. Does it work now? If so, this needs to be highlighted on the Talend Jira site with an example.
@rhall: is this what you mean?
tRestRequest --> tJavaRow ( read some data from the request ) --> tMSSqlConnection --> tMSSQLOutput (Save the data from request) -->
Set: (TJAVA) --> globalMap.put("myLastInsertedID", input_row.last_insert_id );
First request comes at 10:00:01 -->
Set: (TJAVA) --> globalMap.put("myLastInsertedID", input_row.last_insert_id );
id=123 (what is set in globalMap)
Second request comes at 10:00:02 -->
Set: (TJAVA) --> globalMap.put("myLastInsertedID", input_row.last_insert_id );
id=124
At 10.00.03, the original first request is still not completed (as it needs to do some additional processing). So, even at this time, the (Int) globalMap.get("myLastInsertedID") value will return "123" and not 124?
I am just trying to understand the flow here before making any changes. Please advise.
Yeah, that is pretty much what I was suggesting. Personally I would use the tJavaRow or tJavaFlex rather than the tJava, since I only use the tJava for stuff that isn't connected by a row link. However it would probably be OK with a tJava (definitely OK with the others though).
@rhall: Sorry, my bad, I am also using tJavaRow.
But a certain places, I am using tjava too and I got confused.
Also, this newly retrieved value, should I use directly like:
(Int) globalMap.get("myLastInsertedID") or can I save in a local variable and then read it? eg: int myID = (Int) globalMap.get("myLastInsertedID") ;
I just do not want to re-establish my same problem again. Thanks!
You should be OK with a local variable ( for readability it is nicer, I know), but maybe try with just the globalMap first. You don't want to introduce too many changes at first.
I faced the same problem again... the GlobalMap thing does not seem to work. I saw a completely different instance being assigned and used. Any thing else I can try will be appreciated.
I've just had a thought, I don't think this is down to thread safety. I believe the tMysqlLastInsertId makes use of the LAST_INSERT_ID() MySQL function. As such, this is not controlled by Talend. So, if you have 3 service calls being made concurrently (1,2 and 3) it could quite easily end up with a sequence of events like this....
1 ---------->Insert Done-------->Last ID Called Done (3) 2---->Insert Done------->Last ID Called Done (1) 3----------------->Insert Done--------->Last ID Called Done (3)
I think the safest way of managing this is to use a tMySQLRow and retrieve the next ID from a sequence. This way you can supply the id in your insert and know that you have a hold of the correct ID. However I guess your DB may not be able to be changed. In which case the safest way of achieving this would be to carry out a lookup query immediately after the insert to find the correct ID using values you have supplied to carry out the lookup. This will be much slower, but will solve your issue....if you have the lookup values to work with.
