Sccext - Table Locking Errors When Updating Complex Costs For Component Item Cost Changes (Doc ID 2847632.1)

Last updated on FEBRUARY 27, 2024

Applies to:

Symptoms

When a complex buyer pack has multiple items having cost changed across multiple cost changes and sccext is multi-threaded, the batch aborts with the following error:

err.sccext_16.Aug_11:sccext_16~20210811011648~process~~148~ -148: non-ORACLE exception ~INTERNAL STORED FUNCTION ERROR: function=COST_EXTRACT_SQL.BULK_UPDATE_COSTS, error=@0TABLE_LOCKED@1ITEM_SUPP_COUNTRY_LOC@2102282912,cost_chg=2010399

This is because when a component item has its cost changed, the code will then update the cost of all the complex packs associated to that component item. Since a complex pack can have many items, in the event these items are updated in multiple cost changes then multiple threads will try and lock/update the same ITEM_SUPP_COUNTRY_LOC records as the same time.


ERROR
-----------------------

err.sccext_16.Aug_11:sccext_16~20210811011648~process~~148~ -148: non-ORACLE exception ~INTERNAL STORED FUNCTION ERROR: function=COST_EXTRACT_SQL.BULK_UPDATE_COSTS, error=@0TABLE_LOCKED@1ITEM_SUPP_COUNTRY_LOC@2102282912,cost_chg=2010399




STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1) Identify a complex buyer pack with 40+ items, all ranged to over 2500+ locations.
2) Create individual cost changes for the component items.
3) Set sccext to be multi-thread with 16 threads.
4) Run sccext.
5) Notice most threads will abort.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.