Reg Dead Locks while upserting data

panchusrao2656 · Post by **panchusrao2656** » Thu Jul 13, 2006 2:55 pm

Do we need to repartition the data before loading to a table based on the columns which constitutes a primary key on the table.

I am getting the following error.

PROCESS_AUDIT_PARAMETERS,1: Array execute failed for insert:
insert is: INSERT
INTO
FDW_PROCESS_AUDIT_PARAMETERS
(NM, VAL, PRCS_AUD_ID)
VALUES
( :NM, :VAL, :PRCS_AUD_ID)
sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource

splayer · Post by **splayer** » Thu Jul 13, 2006 2:59 pm

This is not a registry error. This is a PL/SQL error. Most probably other
processes are running against this table and causing a deadlock. You need to
find out from your DBA which other processes are running against this table.

kcbland · Post by **kcbland** » Thu Jul 13, 2006 3:15 pm

Something in the table is being locked, indexes, partitions, etc by someone other than you. Your load is using DML instead of sqlldr. If someone/something else is sqlldr'ing the table with DIRECT path loads or such you can experience these typs of errors.

Get your DBA involved.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 13, 2006 5:15 pm

... or it might be a "self deadlock" caused by trying to run the same stored procedure from multiple processing nodes, each trying to lock the same resources. Your DBA will be able to tell you if that's what it is. If it is, you will probably need to run that stage in sequential mode.

panchusrao2656 · Post by **panchusrao2656** » Thu Jul 13, 2006 5:49 pm

Do we really need to partition the data before loading into Oracle when ever there is a primary key index. Is it a regular practice or does Datastage automatically handles such type of situations.

The partition i am using is Auto and the data was not partitioned.

Upsertmode is Insert then Update ( condition on update is 1=2)

kcbland · Post by **kcbland** » Thu Jul 13, 2006 5:58 pm

If the target table is partitioned and you have global indexes (spans all partitions), you get into issues when multiple parallel loads are running against named partitions. Those loads are trying to manage the same index simultaneously and you get problems like you're having.

Is your table partitioned? Do you have locally managed indexes? Is your primary key index global?

panchusrao2656 · Post by **panchusrao2656** » Thu Jul 13, 2006 6:04 pm

Table is not partitioned and the Primary Key index is also not global.

splayer · Post by **splayer** » Thu Jul 13, 2006 7:18 pm

ray, the "self deadlock" concept is an interesting one. I am assuming that panchusrao2656 is running the DML in a stage, most probably an Ora stage? Can you explain a little bit more how "self deadlock" might happen in this situation (upsert)?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 13, 2006 8:57 pm

On an upsert it's highly unlikely since only individual record-level locks are taken, and these should be independent in the different streams. I was making the point that stored procedures can contend for the same resource; if the same procedure is invoked from parallel streams (multiple processing nodes) then any one may lock out the others. DataStage has no control over what "they" put in their stored procedures!

kcbland · Post by **kcbland** » Thu Jul 13, 2006 9:29 pm

You can deadlock yourself if you have parallel streams loading different table partitions with a global index. This is why locally managed indexes should be highly considered, because you can load a partition without conflict to other partitions. When two partitions are contending to update the same index this can happen.

For PX to shine in Oracle shops, you have to consider doing the following:

1. partition tables (favoring anything but ranged partitions for evenly distributed loading performance, but query and maintenance performance goes in the toilet)
2. locally manage your indexes
3. eschew primary key indexes and favor unique locally managed indexes on your non-enforced primary key
4. model your warehouse to limit physical updates and favor more insert only processing (can be done if true Kimball star schemas used for facts, no high volume updating)