Oracle Connector Deadlock Issue

balu536 · Post by **balu536** » Mon Nov 25, 2013 8:57 am

Hi,
My Datastage job loads data to Oracle table with below properties

Write Mode: Update then Insert
Table Action : Append
Record Count : 100000
Array Size: 2000

Today the job failed (Update Query failed) with Deadlock issue
"The OCI function OCIStmtExecute returned status 1. Error code: 60, Error message: ORA-00060: deadlock detected while waiting for resource."

"The connector is not able to resume the write operation because multiple arrays of data were written to the database on one or more input links under the transaction that was active at the time of failover."

Is this because of Record Count and Array size being different. For Update operations, do we need to keep them same?

From: http://pic.dhe.ibm.com/infocenter/iisin ... 4050W.html

Please suggest.

chulett · Post by **chulett** » Mon Nov 25, 2013 9:24 am

Improper partitioning, I would think. More than one node? If so, how are your target streams partitioned? See if hash partitioning over the key fields helps.

balu536 · Post by **balu536** » Mon Nov 25, 2013 9:32 am

Job is running on Two nodes.

I did hash partitioning on Primary Key on the input link of Oracle Connector Stage and updated Record Count to 2000.

Still the Job fails:(

srinivas.g · Post by **srinivas.g** » Mon Nov 25, 2013 9:40 am

Did you check any other process is trying to update the same table?

balu536 · Post by **balu536** » Mon Nov 25, 2013 9:47 am

No other user or process is using the table.

srinivas.g · Post by **srinivas.g** » Mon Nov 25, 2013 10:04 am

Ok. Put record count is 0 and try

balu536 · Post by **balu536** » Mon Nov 25, 2013 10:22 am

I was also thinking to do the same (that wll work).
I'm dealing with 1.5Million data, so shouldn't be a problem for now.

Thanks Srinivas.

balu536 · Post by **balu536** » Mon Nov 25, 2013 1:25 pm

Since i have very less data, ran the job on One node and this might work for me.

Craig, Any suggestion on above?

chulett · Post by **chulett** » Mon Nov 25, 2013 3:17 pm

Running it on one node would certainly rule out partitioning issues if they continue to occur.

balu536 · Post by **balu536** » Tue Nov 26, 2013 9:08 am

Thanks.
Implemented the same and deployed the code to Production.

Hope it don't abort again..