Re: MERGE statement with parallel DML enabled deadlocks itself

From: Mohamed Houri <>
Date: Wed, 6 May 2015 21:10:56 +0200
Message-ID: <>


Just to rule out a possible deadlock situation I have observed recently which is due to an update of the partition key which causes the partition to move and locked the whole table as a consequence.

You are not updating the partition key? right?

Best regards
Mohamed Houri

2015-05-06 11:04 GMT+02:00 Thomas Kellerer <>:

> Hello list,
> we have a fairly large MERGE statement that does some aggregation on a
> reporting table
> with about 12 millions rows. The MERGE only has an UPDATE part, but no
> INSERT, no primary key
> or foreign key columns are changed by the UPDATE (only columns holding
> aggregates)
> The target table is list partitioned on one column with 33 partitions. The
> SELECT statement
> inside the merge joins two tables that are partitioned in the same way as
> the target table.
> On the customer site, this MERGE is executed with parallel DML and
> parallel Query enabled and this
> MERGE repeatedly errors out with a ORA-00060 (although not always). With
> similar (but not identical)
> data in our development environments (but the same base Oracle version)
> this does not happen.
> The deadlock also doesn't happen all the time (which isn't really
> surprising though)
> I found some articles on the Oracle support site reporting problems with
> parallel DML and deferred
> segment creation. And indeed for some partitions no segments where yet
> created. So we forced the creation
> of the segments using "modify partition ... ALLOCATE EXTENT" but
> unfortunately this did not change anything.
> When looking at the deadlock graphs from the trace file, the blocking
> sessions are all sessions
> that are initiated by the parallel DML/Query (the process names are all
> P000 through P003, the
> statement is running with parallel 4).
> The trace files contain a total of up to 65 deadlock graphs and all
> blocked sessions are waiting
> for "enq: TX - row lock contention" with one of the P00x sessions being
> the blocking session.
> I have searched the Oracle support site, but nothing pops up that seems to
> fit this situtation
> The Oracle version is running on Windows Server (not sure which
> Windows version though)
> Any ideas?
> Regards
> Thomas Kellerer
> --


Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <>

Visit My         - Blog <>

Let's Connect -
Profile <>*

My Twitter <>      - MohamedHouri

Received on Wed May 06 2015 - 21:10:56 CEST

Original text of this message