Load DWH job (Full Load) fails with deadlock error

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load DWH job fails with deadlock error:

Encountered an error while truncating table dwh_tme_period : deadlock detected

Environment

Release : Any Supported Release

Resolution

• When running Full Load we never recommend running reports as you also risk reporting wrong data in addition to potential deadlock if one of the tables being used by the report is also being attempted to truncate by the job
• Deadlocks is hard to predict meaning same resource at that given time should be used by both processes which does not happen all that often but again it is a possibility
• With respect when 2 reports running at the same time, normally with 2 select queries we don't see deadlock, usually it's when exclusive lock is required which normally with update/truncate/delete and so on but not for select