Dear All,
I have a parallel extender job which reads records from a sequential file. I am using upsert write method with 'Auto-generated update only' mode to update the record. There are 3 key fields defined in the actual oracle database table.
Key1, Key2, Key3.
My update is based on key1 only, so I have defined only this field as the key in the oracle stage.
When I have run my job with one node set in the configuration file ($APT_CONFIG_FILE), the job went through fine. Now when I have repeated the execution with 4 nodes, the job is getting aborted and the following error is being thrown.
Please help if any of you have any solution to this. Any information is also very helpful to me.
WriteOrd1.LkOutToWriteOrd1,3: Open failed for update. Update is:
sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource
Thanks in advance,
Srinivas.
Problem when processing PX job with 4 nodes
Moderators: chulett, rschirm, roy
-
dlvsrinivas
- Participant
- Posts: 18
- Joined: Mon Jul 07, 2003 12:56 am
- Location: India
Hi,
I'm no PX expert but all I can think about this is:
It seems that your performing parallel updates, therfore beeing caught in a deadlock.
I think you might find that your lock level might not be apropriate (in the DB table).
you should try working in row lock level, so only the row you update is beeing locked; there is a down side for this lock level beeing resources heavy - each row updated is locked seperatly so it will be slower as well.
you might want to try and make an exclusive lock, if possible, on the updated table, but since you have PX job I don't know if the parallel mechanism is able to share the lock mechanisim.
so try to manipulate the lock level.
another thing might be, if you can, work with dirty read - means you might need to build a rollback method in case of failiours.
that is all I can think of for now
IHTH (I Hope This Helps),
Roy R.
I'm no PX expert but all I can think about this is:
It seems that your performing parallel updates, therfore beeing caught in a deadlock.
I think you might find that your lock level might not be apropriate (in the DB table).
you should try working in row lock level, so only the row you update is beeing locked; there is a down side for this lock level beeing resources heavy - each row updated is locked seperatly so it will be slower as well.
you might want to try and make an exclusive lock, if possible, on the updated table, but since you have PX job I don't know if the parallel mechanism is able to share the lock mechanisim.
so try to manipulate the lock level.
another thing might be, if you can, work with dirty read - means you might need to build a rollback method in case of failiours.
that is all I can think of for now
IHTH (I Hope This Helps),
Roy R.
Hi,
Regarding the PX side of the problem, remember that every partion will open a connection to your DataBase. You must therefore make sure that your records are well partitioned. A deadlock may occur if more than one process tries to access the same records. Make sure that your PX partion respects your DataBase key(s).
hope this helps
Mat
Regarding the PX side of the problem, remember that every partion will open a connection to your DataBase. You must therefore make sure that your records are well partitioned. A deadlock may occur if more than one process tries to access the same records. Make sure that your PX partion respects your DataBase key(s).
hope this helps
Mat