Oracle Row Locking

[Raftsman]

Configuration: 2 nodes

Datastage Job Oracle EE Source stage ====> Oracle EE Target

Row count : 100000

During the run, the Oracle job hung. After investigating the hung job with the Oracle DBA, it was found that row locking was the problem. For some reason, Node 1 blocked Node 2 from inserting to the database.

Has anyone encountered this?

I thought the purpose of parallelism was to take advantage of dual processing. From what I can see, it's causing a problem.

Can anyone provide some insight on this.

Thanks

[ogmios]

It's not a DataStage problem, it's Oracle and it's not a problem.

In Oracle when you update the same row at the same time from different sessions you block each other. In order to use parallellism the right way you have to make sure that you divide up rows and that no 2 sessions try to update the same row.

[Raftsman]

Let me further explain. There is only one job processimg one table. A straight read and write. What I am not sure about is on the source Oracle EE stage it reads in sequential mode. On the target Oracle EE stage the default execution mode is parallel (which means it processes data on two nodes. You would think that the Oracle EE stage would direct the data and not cause the same record to be processed on two nodes. This is where I am lost. What is the best course of action. I thought the data would be processed quicker on two nodes.

[Raftsman]

I just found out that the source system change a key datatype length that cause Oracle to hang through DataStage. Looks like Ogmios was correct. It wasn't a DataStage problem. I change the target database and everything worked fine.