Reg Dead Locks while upserting data
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
Reg Dead Locks while upserting data
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
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
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.
Get your DBA involved.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
... 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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
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)
The partition i am using is Auto and the data was not partitioned.
Upsertmode is Insert then Update ( condition on update is 1=2)
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?
Is your table partitioned? Do you have locally managed indexes? Is your primary key index global?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Charter Member
- Posts: 64
- Joined: Sat Sep 17, 2005 10:42 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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)
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)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle