Transaction handling in Parallel jobs

Amuktamalyada · Post by **Amuktamalyada** » Fri Feb 23, 2007 9:25 am

Hi everyone,

I have a parallel job that has failed due to a deadlock on one of the Oracle tables into which the job does inserts.

The approach I adapted was to see if there was any concurrent process that does any modification to the table and found out to be none.
But, I am not sure about the transaction commit process in Parallel jobs which could be another reason for the deadlocks.

Please advice if I am heading the right way in analyzing my issue.

Thank you,
Mukthi

DSguru2B · Post by **DSguru2B** » Fri Feb 23, 2007 9:39 am

What led you to believe that the table was locked? or your process was deadlocked? If you job hangs then I suggest getting together with your dba to see if the connection even reaches the table and whats happenening at the database level. There might be live threads out there with your id which might have put the table in a locked state.

Amuktamalyada · Post by **Amuktamalyada** » Fri Feb 23, 2007 10:05 am

I came to know about the deadlock from the job log in production.

DSguru2B · Post by **DSguru2B** » Fri Feb 23, 2007 10:23 am

If this is DataStage log then it would be nice to post the exact error message.
If you suspect its the commit level then there are two environment variables that you can set to control commit levels. Namely
APT_ORAUPSERT_COMMIT_ROW_INTERVAL
APT_ORAUPSERT_COMMIT_TIME_INTERVAL

Amuktamalyada · Post by **Amuktamalyada** » Fri Feb 23, 2007 10:42 am

Thank you Sir,

The error log goes as below.

insert is: INSERT
INTO
abcd
(a,b,c.d...)
VALUES ( 1,2,3,4...) sqlcode is: -60 esql complaint: ORA-00060: deadlock detected while waiting for resource.

I am glad to know more about the env vars.

Thanks again,
Mukti

DSguru2B · Post by **DSguru2B** » Fri Feb 23, 2007 10:52 am

I highly doubt its a commit level issue. It was just the timing at which your job ran. The table was locked by another process/session. Run the job again and see what happens.

Amuktamalyada · Post by **Amuktamalyada** » Fri Feb 23, 2007 4:03 pm

I did not find any concurrent process running on that table and hence was I intended to check for the transaction commits.

-Mukthi

ray.wurlod · Post by **ray.wurlod** » Fri Feb 23, 2007 4:16 pm

Does the job itself have an active SELECT on the same table?

Amuktamalyada · Post by **Amuktamalyada** » Fri Feb 23, 2007 4:53 pm

Yes Ray, the job has select in itself which occurs when it looks up with 5 Oracle tables then the output from the lkp gets inserted into the table abcd(Table that is getting locked).

Thank you,
Mukthi

ray.wurlod · Post by **ray.wurlod** » Fri Feb 23, 2007 5:02 pm

If you have an active SELECT cursor on table abcd, then table abcd will be locked against INSERT operations. Reduce your transaction size to 1 row, and/or set to auto-commit. Even better, select the data into a staging area (a persistent Data Set or Lookup File Set) in a prior job to avoid a self-deadlock situation.

Amuktamalyada · Post by **Amuktamalyada** » Sun Feb 25, 2007 9:12 pm

Ray, the table abcd(the one that is getting locked) does not have a select in this job other than an insert(which is failing).There are 5 other Oracle tables that have a select performed on them.

Thank you,
Mukthi

DSguru2B · Post by **DSguru2B** » Sun Feb 25, 2007 10:12 pm

How many open cursors are allowed by your id? There might be a limit to the number of open connections allowed by your id. Your DBA will be able to tell you how many connections are allowed by your id.

Amuktamalyada · Post by **Amuktamalyada** » Mon Mar 05, 2007 9:36 pm

Hi all,

I am not sure if this can be called a resolution. However, the solution came by changing the process flow of the active stages from parallel to sequential.

Thanks,
Mukthi

DSguru2B · Post by **DSguru2B** » Mon Mar 05, 2007 9:46 pm

Your target database might not be partitioned. Multiple inserts to a table should be fine too. Did you try commit level of 1?

kumar_s · Post by **kumar_s** » Tue Mar 06, 2007 1:33 am

Does you table have partition in Database?
What is the partition method used to partition data to 5 streams?
Could you explain the job design a bit more?