DataStage Job "ABORTED" because of Deadlock issue

h4harry1 · Post by **h4harry1** » Fri Sep 27, 2013 8:05 pm

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.

ArndW · Post by **ArndW** » Sat Sep 28, 2013 5:37 am

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?

h4harry1 · Post by **h4harry1** » Sat Sep 28, 2013 6:14 am

Thanks for replying back.

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

dboption Mode-- Auto Generated

Thanks