To Avoid a Possible Deadlock

[Amit_111]

Hi All,

I have a Flag in my Job based on which I have to either Insert a record or Update the record in the target.

While Update i have to update only 3 columns out of 10 thats the reason i cannot use "Insert/Update Option" in a single link.

I searched the forum for such cases and found that i can acheive this using two seperate links passing to the same target DataBase stage. Doing this will avoid the the possible Deadlock scenario (which i would have faced incase if i go for seperate DB stages for Insert & Update). Also I will need to keep the Commit Size as 1.

I just want to be 100% sure that deadlock wont happen in this case(using a single target DB stage with two I/P links ). May be all those peoples who have sucessfully implemented such a scenario can please provide your inputs regarding the same.

Please let me know if there are any risks in this apoproach or its safe to go for two seperate jobs(which i dont want to) instead of using the a single DB stage with two links.

Please provide your inputs.

Thanks in Advance.

[kris007]

DB stage does not support two input links. What you can do is use the two separate links - one for update, one for insert and use a funnel stage with those two input links and the output of the funnel stage goes to your DB stage. That way, there is only one link that accesses your DB and you won't have any deadlocks.

Hope that helps.

[priyadarshikunal]

DB stage does not support two input links.

In server jobs it does.

While Update i have to update only 3 columns out of 10 thats the reason i cannot use "Insert/Update Option" in a single link.

you should be able to do it with user defined Query.

Doing this will avoid the the possible Deadlock scenario (which i would have faced incase if i go for seperate DB stages for Insert & Update). Also I will need to keep the Commit Size as 1.

As Most of the databases won't put a table lock for update queries, and will put row locks instead. Hence if you are updating it on surrogate key or unique key to avoid the records getting inserted concurrently, It should not create a problem.

I generally prefer to break the jobs in to 2 in case it may cause any contention. But in this case you can do it in same job, provided you give enough constraint in your update query.

[chulett]

You don't need "user defined" anything for this, the two links into the one stage will support generated sql just fine. And you certainly won't need the Transaction Size as 1 for this although you do need to be careful with the Array Size if there is any chance you are updating records you also have inserted in this run as the insert may not have been 'pushed' to the database yet.

[Amit_111]

Sorry, By saying DB I meant to use Database stage i.e. Oracle wherein two links in a target stage will be possible. So i hope i can go ahead with this design and avoid causing possible deadlocks.