Locking of Table

[Sreenivasulu]

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

[sanjay]

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

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

[Sreenivasulu]

Thanks Sanjay.
Creating a seperate job for these works but i wonder why it does not
work in a single job since datastage processes records one after another
(In server jobs)

Regards

[ray.wurlod]

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.

[chulett]

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.

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.

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?

[sara123]

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

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

[bapajju]

Ray,
Kindly let us know if there is any round about way to handle Update and Insert in a single job.


Thanks

[bapajju]

Ray,
Kindly let us know if there is any round about way to handle Update and Insert in a single job.


Thanks

[bapajju]

Hi Srini,
We r also getting the same Lock Issue. Kindly let us know if you have got solution to this by now.

Thanks

[rasi]

Send the insert and update to two different text file and use them in the same job to do insert and update. This will be the work around if you want that to happen in the same job.

Cheers
Rasi

[ray.wurlod]

Ray,
Kindly let us know if there is any round about way to handle Update and Insert in a single job.
Thanks

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.
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.

[chulett]

Kindly let us know if there is any round about way to handle Update and Insert in a single job.

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.

There may still be issues with certain databases for all I know, but can tell you this technique works fine in Oracle.

[jiegao]

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.

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

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

[sachin1]

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.