MSCPDC Fails With ORA-00060: Deadlock Detected While Waiting For Resource
(Doc ID 755419.1)
Last updated on APRIL 04, 2025
Applies to:
Oracle Advanced Supply Chain Planning - Version 11.5.10 and laterInformation in this document applies to any platform.
EXECUTABLE:MSCPDC - Planning ODS Load
Symptoms
When attempting to run collections the following error occurs:
Index Dropping on Temp Tables successful.
<>
<>CREATE INDEX SYSTEM_ITEMS_N1_EAO ON
SYSTEM_ITEMS_EAO(PLAN_ID,PLANNER_CODE,ORGANIZATION_ID,SR_INSTANCE_ID,INVENTORY_ITEM_ID) PARALLEL
TABLESPACE APPS_TS_TX_IDX PCTFREE 10 INITRANS 11 MAXTRANS 255
ORA-12801: error signaled in parallel query server P005
ORA-00060: deadlock detected while waiting for resource
<>
<>ALTER TABLE MSC_SYSTEM_ITEMS EXCHANGE PARTITION SYSTEM_ITEMS__1 WITH TABLE SYSTEM_ITEMS_EAO INCLUDING INDEXES
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Test Case Example
They have a third party POS software that is calling the Oracle Order API. Sometimes every second like on weekends when people are buying their items. The Oracle API updates the msc_sales_orders table at the same time that Net Change Collections is updating msc_sales_orders table.
* Note the test environment did not have this kind of volume so hence the deadlock did not occur.
Yes, Oracle OrderAPI and Net Change Collections are seeded Oracle processes but first we need to determine the following:
1) Was Oracle Order API designed to be run at the same time as Net Change Collections especially since both seem to touch the MSC_SALES_ORDERS table? Do they respect each other and line up in a queue to make their update or do they just jump the queue and try to jam in the update at the same time thus resulting in a Deadlock error?
2) If Oracle Order API was indeed meant to run at the same time as Net Change Collections, ex. it could handle records being inserted and/or updated into msc_sales_orders table, then is the problem that the Order API was not meant to be called this often, potentially every second, by this third party POS System
Below statement was running through Oracle API.
Answer
Demand updates are real time, so it is not necessary to run Net Change of Sales orders. Since your real business concern is updating On hand and PO/REQ supplies as close to real time as possible, so running Targeted Refresh of just those two entities should satisfy your business requirements. Then run Net Change or Complete Refresh of sales orders overnight, complete is recommended.
Another benefit of this plan is that the Targeted Refresh should be faster than the Net Change Collections.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Test Case Example |
| Answer |
| Cause |
| Solution |
| References |