DSXchange

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

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.

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

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

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

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.

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

-Mukthi

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

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

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.

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

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.

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

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

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?