Frequent deadlocks

[bella0813]

Lately we have been having frequent deadlocks in one of our tables. THe number of rows have not increased/ Infact they are pretty consistent. We have an etl job that does an insert/update into a table in ORACLE>
This job is runnin in multiple nodes

The deadlocks error were of the kind :

sqlcode is: -60
esql complaint: ORA-00060: deadlock detected while waiting for resource

After which we had the inittrans of the index increased from 2 to 10..a week after we still have issues, but this time we are getting blocked sessions. the blocker and the waiter are the 2 different nodes from that job.

from the source(where the data is read) :
EXECUTION MODE: default sequential
COMPATIBILITY MODE: (AUTO)
RESERVE PARTITIOINING DEFAULT (CLEAR)

NodeMAP COnstaitn is enabled(checked)

In the taget table:

The partitioning type is auto.


Any clue on which attribute should change. The data from the source is identified uniquely based on ID. The result set should all be unique.

THanks for your help

[/img]

[arun_im4u]

As search for

deadlock detected while waiting for resource

retruns 19 matches. See if any of those help you in resolving the issue.

[chulett]

Two typical solutions - run on a single node or switch to hash partitioning over the 'appropriate' columns. Have you tried either?

[bella0813]

Thanks, I am attempting to change to Hash Partitioning. I carried out a test before making this change and found the input Link to the target table shows 1222 ROWS.

But on changing the partitioning to HASH from AUTO, the input shows 611 records,

Checking the database I can tell- it probably processed all of those (1222 rows),

But unsure why changing to hash will show half the number of rows on the link. (Running on 2 nodes- does that have anything to do with it?)

thanks for your help,.

[ArndW]

If you open up a monitor window and then right-click on it, you can select the option "Show Instances" and that should answer your question.