-
Title
Warning: Mtp is deadlocked. Cause is unknown (due to DLOAD) -
Description
On source, there is a DELETE and followed by INSERT /*+APPEND*/ into .. select * from ... (DLOAD) followed by one commit all done in one oracle session.
There is a unique index on this table.
On target, we saw two sessions blocking each other. One session is doing DELETE and the other session is doing DLOAD. MTP is hung and gets "Warning: Mtp is deadlocked. Cause is unknown". It started killing sessions and restarting and never get out of this loop.
If you switch to STP, post gets "Post stopped due to obreak". -
Cause
Software bug, Oracle assigns a different transaction id for DLOAD which causes DELETE and DLOAD assigned to different subques (sessions) on target.
-
Resolution
WORKAROUND 1:
Change DELETE to TRUNCATE if you are deleting all records in a table.
WORKAROUND 2:
Remove /*+APPEND*/ hint from INSERT.
STATUS:
Issue fixed in 5.3.4.50. -
Defect ID
ST43433 -
Additional Information
Converting unique indexes on target to non-unique indexes may temporarily get MTP post out of the loop, but this may result in out of sync for this table.