Data Warehouse job fails with deadlock on Jaspersoft tables

book

Article ID: 143831

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse fails with error 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 : CA PPM JASPERSOFT

Resolution

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. 

 

To resolve this issue, please run the Full Load again, and see if it goes through. 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.