Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Deadlock during parallel update of non partitioned table

977673Dec 3 2012 — edited Dec 4 2012
Hello,
I'm new to the forum and I hope someone would be able to provide some advice as far as issue described below we experience in one of our production systems.

We have been experiencing sporadic deadlock issues with the statement like the one listed below:

UPDATE /* parallel(16) */ t1
SET col1 = 'some value'
WHERE EXISTS
(
SELECT 1
FROM t2
WHERE t1.id = t2.id
);

Both tables are non partitioned with 65M+ rows each. Number of updated rows may be significant percent of the table contents so parallel update in our case was fastest solution. The optimizer unnests subquery and hash joins t1 with t2 to perform parallel update. However sometimes the statement causes deadlocks. There is nothing else running on the system at that time, there are only b-tree indexes on the table, so no bitmap indexes, there are no triggers and no constraints on the updated column. We increased INITRANS and rebuilt the tables and indexes on them (with higher value than DOP used in DML). But still from time to time we are experiencing deadlocks. The only response we received from our DBA team and the Oracle Support was that deadlocks are really an application issue and we should perform update as a serial statement.

So my question is:

1) Is the statement type listed above a valid statement which should run correctly?
2) If it is a valid statement are there any other situations which can cause deadlocks (except ITL shortage, missing indexes on FKs, conflicting transactions)?
3) Are there any oracle bugs related to deadlocks which may apply here? (I did some search online, however haven't found anything interesting)
4) Can we turn on more tracing to capture information which could help solve the riddle? If yes what would that tracing be?

Thank you for any advice/help,

Sincerely,
Marek
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 1 2013
Added on Dec 3 2012
10 comments
1,383 views