Locking of Table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Locking of Table
Hi All,
I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job
hangs.
Regards
I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job
hangs.
Regards
Re: Locking of Table
Try creating a separate job for insert and upate because constraint does n't wait for insert or update to happen first. that is why table get locked
Sanjay
Sanjay
Sreenivasulu wrote:Hi All,
I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job
hangs.
Regards
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The separate links attempt to open separate cursors into the table. Whichever operates first manages to set an exclusive update lock on the table until the COMMIT arrives, which means that the other can not.
An even better approach is not to insert or update rows at all, but to create two separate data files for the bulk loader. Run the bulk loader to perform the inserts first, then run the bulk loader again to perform the updates.
An even better approach is not to insert or update rows at all, but to create two separate data files for the bulk loader. Run the bulk loader to perform the inserts first, then run the bulk loader again to perform the updates.
Last edited by ray.wurlod on Wed Jun 09, 2004 5:23 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Locking of Table
You've gotten good advice about separating processes like this. The odd thing is what you are doing should generally work fine. We do it all the time for small record sets.Sreenivasulu wrote:I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job hangs.
Are you sure your updates and inserts are distinct transactions and are not colliding with each other? What does your DBA say is going on in the database when the job hangs? Is it truly a table lock or are you actually getting row deadlocks? I'd also check your Update Actions, make sure you are not using 'Completely replace existing row'...
One typically sure-fire way to fix it is to set your Transaction Size to 1 so that each row commits as it is written, but that's less than ideal. Speaking of which, what kind of Array Sizes are you using?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Locking of Table
Hi ,
While doing Update/Insert on same table.
1) Create single Target
2) Create two links to single Target Stage ( Insert Link & update link)
3) Please check Parameter Array size and transaction size ( example: 125 and 500 per transaction size).
if you use this technique you should not get locking issue.
Thank you
kn
While doing Update/Insert on same table.
1) Create single Target
2) Create two links to single Target Stage ( Insert Link & update link)
3) Please check Parameter Array size and transaction size ( example: 125 and 500 per transaction size).
if you use this technique you should not get locking issue.
Thank you
kn
Sreenivasulu wrote:Hi All,
I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job
hangs.
Regards
Re: Locking of Table
Hi Srini,
We r also getting the same Lock Issue. Kindly let us know if you have got solution to this by now.
Thanks
We r also getting the same Lock Issue. Kindly let us know if you have got solution to this by now.
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Run the inserts into a bulk loader stage with automatic run, and the updates into a sequential file stage, then use an after-job subroutine to trigger the bulk load of the updates.bapajju wrote:Ray,
Kindly let us know if there is any round about way to handle Update and Insert in a single job.
Thanks
This is one solution. There are almost certainly others. I didn't give it a great deal of thought, as I'm catching up on a week away from DSXChange.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
It's already been mentioned by Sara123. I've used it extensively and don't have any 'locking' issues. The key is to use two links to a single target stage, not two seperate target stages.bapajju wrote:Kindly let us know if there is any round about way to handle Update and Insert in a single job.
There may still be issues with certain databases for all I know, but can tell you this technique works fine in Oracle.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Locking of Table
I have the same lock issue. Most of the times it works fine. But sometimes it just hangs there waiting for lock to release. I am wondering if array size afffects the locks. Currently my array size is larger than 0 and rows per transaction are all set to 1.
Thanks in advance.
Thanks in advance.
sara123 wrote:Hi ,
While doing Update/Insert on same table.
1) Create single Target
2) Create two links to single Target Stage ( Insert Link & update link)
3) Please check Parameter Array size and transaction size ( example: 125 and 500 per transaction size).
if you use this technique you should not get locking issue.
Thank you
kn
Sreenivasulu wrote:Hi All,
I am updating/inserting in a table with a single server Job (There are two target stages for the same table) - one with an update on one condition and another with insert with another condition. Both constraints result in two different targets. But the table(not job) gets locked. Hence the job
hangs.
Regards
Regards
Jie
Jie
Re: Locking of Table
i daily use this kind of situation regarding insert and update, with Array Size:1500 and Transaction Size:50, I never get a lock issue, Array Size and Transaction Size need to be checked, i agree with sara123.