DWH job fails with deadlock on Jaspersoft tables

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse (DWH) fails with errors as follows: 

ERROR BasicLoggingFilter,http-nio-8080-exec-13:70 [XXXXXX|ppmjasperadmin] - Exception during flushing audit context

org.springframework.dao.CannotAcquireLockException: Could not execute JDBC batch update; SQL [insert into JIAccessEvent (user_id, event_date, resource_id, updating, id) values (?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessE

---

2019-12-24 15:16:59,829 ERROR AbstractFlushingEventListener,http-nio-8080-exec-14:325 [XXXXXXX|ppmjasperadmin] - Could not synchronize database state with session

org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

---

2019-12-24 15:16:59,820 ERROR ExternalDataSynchronizerImpl,http-nio-8080-exec-13:96 [XXXXXX|cccccc] - Error during synchronization

com.jaspersoft.jasperserver.api.JSExceptionWrapper: 

org.springframework.dao.CannotAcquireLockException: could not delete: [com.jaspersoft.jasperserver.api.metadata.common.service.impl.hibernate.persistent.RepoFileResource#247381535]; SQL [delete from JIResource where id=? and version=?];

---

ORA-00060: deadlock detected while waiting for resource 

Environment

Release : All supported Clarity releases

Component : CLARITY JASPERSOFT / DATA WAREHOUSE

Cause

This is an issue with the second part of the Data Warehouse job, the "Domain Updates to Jaspersoft". The ETL part of the job is successful, which means that all the data in Data Warehouse is actually correct. What failed is the Domain Update which will add/update any new attributes that you included in DWH to the Jaspersoft domains and views. 

The impact is that you may not be able to see the change in the attributes in Ad Hoc Views or such. 

Resolution

1. Run the Full Load DWH job again, and see if it goes through.
2. If you still have the issue, review the deadlock with your DBA. We recommend enabling Audit for more details.

Usually this happens if the domain was updated from UI whilst the job was running. To avoid the issue, avoid updating the domain whilst the job may be updating it too. 

Additional Information

See also: Load DWH job frequently reported issues