Data Warehouse fails with Deadlock on custom NSQL on DWH db
Article ID: 371069
Updated On:
Products
Issue/Introduction
Data Warehouse job is failing with Oracle deadlock, example:
2024/05/28 10:24:41 - Enable Constraints Oracle - ERROR (version 9.3.0.0-428, build 9.3.0.0-428 from 2022-04-12 04.56.25 by buildguy) : An error occurred executing this job entry :
2024/05/28 10:24:41 - Enable Constraints Oracle - Couldn't execute SQL: BEGIN
2024/05/28 10:24:41 - Enable Constraints Oracle - CMN_ENABLE_CONSTRAINTS_SP('DWH_INV_TEAM_PERIOD_FACTS');
2024/05/28 10:24:41 - Enable Constraints Oracle - END;
2024/05/28 10:24:41 - Enable Constraints Oracle -
2024/05/28 10:24:41 - Enable Constraints Oracle - ORA-20100: ERROR ENABLING CONSTRAINT. SQLERRM : ORA-04020: deadlock detected while trying to lock object PPM_DWH.DWH_CMN_PERIOD
2024/05/28 10:24:41 - Enable Constraints Oracle - ORA-06512: at "PPM_DWH.CMN_ENABLE_CONSTRAINTS_SP", line 27
2024/05/28 10:24:41 - Enable Constraints Oracle - ORA-06512: at line 2
2024/05/28 10:24:05 - Start processing team period facts - dwh_inv_team_period_facts.kjb
2024/05/28 10:24:05 - Load team period facts - dwh_inv_team_period_facts_load(
2024/05/28 10:24:05 - Load team period facts - DBLink: PPMDBLINK
2024/05/28 10:24:05 - Load team period facts - From : to_date('2024/05/28 09:45:01', 'yyyy/MM/dd HH24:mi:ss')
2024/05/28 10:24:05 - Load team period facts - To: : to_date('2024/05/28 10:15:02', 'yyyy/mm/dd HH24:MI:SS')
2024/05/28 10:24:05 - Load team period facts - B Size: 50000)
Note : The deadlock may happen on different tables in DWH
Resolution
- Ask the DBA to provide the Oracle trace to identify the second process
- Check for any custom NSQL portlet querying the DWH database
- If this is the second process that locked in the table, that would cause the deadlock victim to be the DWH job.
- We recommend to redesigning custom NSQL to run on Clarity database instead of DWH and use the DWH views on Clarity database
- We have an enhancement in the backlog to improve this behavior
Feedback
