This discussion is archived
10 Replies Latest reply: Dec 4, 2012 8:56 PM by 977673 RSS

Deadlock during parallel update of non partitioned table

977673 Newbie
Currently Being Moderated
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
  • 1. Re: Deadlock during parallel update of non partitioned table
    sb92075 Guru
    Currently Being Moderated
    does any PK/FK relationship exist between T1 & T2?
    do any BITMAP INDEX exist on either T1 or T2?
  • 2. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    No there are no bitmap indexes.
    Yes there are PKs on both tables and FK on the updated t1 table pointing to parent t2 table. However updated column is not part of any constrainsts.
  • 3. Re: Deadlock during parallel update of non partitioned table
    sb92075 Guru
    Currently Being Moderated
    974670 wrote:
    No there are no bitmap indexes.
    Yes there are PKs on both tables and FK on the updated t1 table pointing to parent t2 table. However updated column is not part of any constrainsts.
    you need to ensure that each & every FK has actual INDEX to support it.
  • 4. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    Alll FK/PK have valid indexes, however here they are not used as parallel FTSs are done on the tables and tables are joined using hash join. Additionally the updated column is not part of any PK/FK.
  • 5. Re: Deadlock during parallel update of non partitioned table
    Sven W. Guru
    Currently Being Moderated
    974670 wrote:
    ... We increased INITRANS and rebuilt the tables and indexes on them (with higher value than DOP used in DML).
    That would have been my first idea for the scenario you describe. How did you change the table and indexes to use a higher initrans value?

    For example an alter table statement is not enough. Also not a truncate+reinsert.

    The initrans parameter is already used for the initial extent. This is not freed/reused easily.
    And this can be changed only if you do a
    alter table move tablespace
    operation (or drop and recreate).

    ...
    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?
    Do you see already the wait events and what kind of waits do you experience? An ITL shortage would be something like an TX 4 wait if I remember correctly.

    In the oracle sql developer the session view will also have a detail tab about wait events and another one about conflicts (blocking locks etc.).

    Edited by: Sven W. on Dec 3, 2012 11:59 PM
  • 6. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    Hi Sven,
    Thank you for response.
    I did "alter table move", and indexes were simple dropped and recreated with higher INITRANS. I don't see any ITL waits for that statement/segment neither for indexes. I do not want to copy here trace files as they probably will not be enough to debug anyways.
    The interesting thing is we can' replicate issue anywhere, so this happens only in one environment, one statement when nothing else is running and it doesn't happen every time. Sometimes when it happens and transaction is rolled back, the next re-run is fine.
    I just wanted to make sure:
    1) The statement type we use is fine(parallel update with exists with correlated subquery)?
    2) Can we do something to get more trace info?
    3) Is there any known bug which could apply here?

    The version of oracle we use is 11 g r2.

    Thank you very much for any ideas.
  • 7. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    One more detail: the statement is executed within PL/SQL block where other update for the same table is executed as well with commit after each statement (I know default commit behaviour changed for PL/SQL blocks, but it probably doesn't matter here, as I tried COMMIT WRITE WAIT without any results anyways)
  • 8. Re: Deadlock during parallel update of non partitioned table
    977991 Newbie
    Currently Being Moderated
    - in the original query there seems to be join missing. optimizer still has to join two tables prior to get to update, right ? make it explicit.
    update t1 ... where exists (select ... from t2 <no t1 here ?> where t1.id = t2.id)

    - other situations to get ora-60 by Tom Kyte ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1528515465282 )
    "... You are updating more then one row in <tab> but must be getting them in a different order so one session
    updated 1 row, another row 5 (for example) then the first session tried to update row 5
    (blocks) and the second session tried to update row 1 (deadlock)..."

    - partition 65+ mln table and update each partition with one (parallel 1) session - must be no locks ;-)

    regards
  • 9. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    I don't think anything is missing in the statement:

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

    It is a simple update in table t1 whenever we have corresponding rows in table t2 and there as join criteria specified as well: "WHERE t1.id = t2.id"
    So it is an update with exists clause containing correlated subquery. So please elaborate more on what do you mean? Are you saying that statement is incorrect? Or oracle doesn't treat this type of updates as valid?

    Tom doesn't describe single statement executed as parallel DML, but a case of multiple statements executed by multiple parallel sessions, so his case is much different.
    In our case because how oracle splits work for a single parallel DML statement, there should be no collisions on rowid level in table t1 and as I understand they can happen on block level, however it should not be the case as we increased INITRANS for table t1 and its indexes.
    As far as partitioning to partition table only to be able to run process serially doesn't quite make sense if we could use simple parallel dml even on non partitioned table.
  • 10. Re: Deadlock during parallel update of non partitioned table
    977673 Newbie
    Currently Being Moderated
    Just to clarify more, I'm not looking for workarounds as we can do in worst case scenario much less efficient serial update, but for answers to the 3 questions I specified above.
    Thank you all of you for your inputs.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points