DSXchange

DataStage -- 8.1
Database -- Oracle 10g
OS -- Unix

I have a DataStage job (FCT) which is doing a lookup based on two keys columns to the DIM table. This job ABORTED with the following error message.
------------------------------
"main_program: (aptoci.C:483). Message: ORA-04020: deadlock detected while trying to lock object DIM_TABLE_NAME"
------------------------------
Partition type for lookup stage -- Auto
Config file Nodes -- 2

Note:- Most of the times this job runs without any issues but sometimes fail with the above error message.

Don't understand what can cause deadlock here and how to resolve this issue.

Thanks in advance.

If we take the problem step-by-step, the first question is:

How are you using the table "DIM_TABLE_NAME" - what stage type, what settings - particularly regarding locking, i.e. are you doing uncommitted reads? User-SQL or auto-generated?

Thanks for replying back.

Stage type :- Oracle Enterprise
Stage Settings :-
--------------------
Lookup Type-- Normal
Read method -- Table
Table-- "Table Name"

dboption Mode-- Auto Generated

Thanks