RE: MERGE statement with parallel DML enabled deadlocks itself

If the problem was ITL waits then I think the reported wait would be "enq: TX  - allocate ITL entry" rather than "enq: TX - row lock wait".  It might be helpful, though, to know whether the TX lock wait in the deadlock graph were mode 6 or mode 4, as the latter tend to suggest an index-related problem or an internal problem.


Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Kenneth Naim [kennethnaim@gmail.com]
Sent: 07 May 2015 16:54
To: thomas.kellerer_at_mgm-tp.com; oracle-l_at_freelists.org
Subject: RE: MERGE statement with parallel DML enabled deadlocks itself

I had a situation where a parallel dml encountered deadlocks on itself, the cause was insufficient space in the block to expand the ITL slots. Rebuilding the table with sufficient ITL slots to support all the parallel threads solved our issue.  I'd increase it to the max number of parallel sessions any query against the table uses.

Hope this helps,
Ken

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Kellerer
Sent: Wednesday, May 06, 2015 5:05 AM
To: oracle-l_at_freelists.org
Subject: MERGE statement with parallel DML enabled deadlocks itself

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 11.2.0.4 running on Windows Server (not sure which Windows version though)

Any ideas?

Regards
Thomas Kellerer

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--