DEADLOCK: ORA-00060 UPDATING TABLE

[sangi1981]

Hello everyone,
I am writing to ask for your help.

I have a job structured as follows:

Code: Select all

DS -> RD -> CONNECTOR (ONLY UPDATE)

This job updates more than 30,000,000 records on target table.

Key of update is a subset of primary key, hence the presence of Remove Duplicate to which I added a HASH / SORT for the same subkey.
I was able to verify that there are no duplicates with respect to the key update.

The target table has many indexes, which cover various fields for various usage needs (we are in DWH environment).

I haven't deadlock when job runs on development test, where degree of parallelism is 2.
Otherwise, I obtain deadlock when job runs on test environment, where DOP is set to 4.
The connector has the default configuration.
I repeat execution of job many times, both on develop and test, avoiding execution of other jobs.
Target table on test env is exposed to test-user throw BO Universe.

I don't understand what is the reason of this behavior.
What I'm missing?
What can cause a deadlock?

Thank you for your help,
Sandro

[chulett]

What can cause a deadlock?

Have you talked to your DBA yet? They love to answer questions like that and should be able to dig up the trace from the alert log for you as well to know more about what is going on.

[sangi1981]

Ohh, Ok.
I will ask to my DBA for trace file.....
Do you think it is some problem related to the table?
What should I expect, in your opinion?

Thanks

[sangi1981]

Hello everyone,
As suggested by chulett, I asked DBA the trace log.
I found a deadlock between two different rows due to a "deadlock" on the same unique index.
On both the rows, the same field was updated; this field is part of the primary key.
Hence the deadlock.

The suggestion of our DBA was to delete all search indexes, and recreate them after the upload.
For as I could tell, with respect to this kind of problem, there is not much else to do. That's right?

Thanks