Reporter Server freezes extractions due to deadlock

Products

• CA Automic Dollar Universe

Issue/Introduction

We're using Reporter with Continous Extractions every X minutes of all our Nodes, since a Migration of both the Windows Server and Database Server to a recent one (2016), Reporter locks the Database MS SQL Server within a few hours after restart.

The only workaround we've found is to relaunch the Windows server for Reporter. This frees up the connections to the Database and unlocks it.

The following error appears in server.log:

[ERROR] 12 Oct 2021 19:14:17 [pool-X-thread-Y] com.orsyp.reppub.server.reporter.extractor.owls.OwlsReporterExtractManager$1 -  ReporterExtractManager process - EXECUTIONS - failedExecution().
java.util.concurrent.ExecutionException: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 159) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 at java.util.concurrent.FutureTask.report(FutureTask.java:122)
[...]

Our DBAs have analyzed the issue and found that this is a lock cycle.

Here's what they told us:

• process 51 is blocked by process 58. Process 51 SQL is :

SELECT COUNT(extraction_id) FROM [DU_Reporter].[dbo].[UNI_EVT_EXTRACTIONS] WHERE STATUS = 'COMPLETED' AND LAST_UPDATE_TIMESTAMP > DATEADD(mi,-10,GETDATE())

 

• process 58 is blocked by process 57. Process 58 SQL is:

(@P0 bigint,@P1 bigint,@P2 datetime2,@P3 datetime2,@P4 int,@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int)insert into UNI_EVT_EXTRACTIONS (DATASOURCE_ID, LOG_ID, START_TIMESTAMP, LAST_UPDATE_TIMESTAMP, RECORD_COUNT, STATUS, EXTRACTION_SET_NAME, LAST_RECORD_TIMESTAMP, LAST_AUDIT_TIMESTAMP, LAST_OEX_TIMESTAMP, CONTINUOUS) values (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10) select SCOPE_IDENTITY() AS GENERATED_KEYS

 

• process 57 is blocked by process 72. Process 57 SQL is  :

(@P0 bigint,@P1 bigint,@P2 datetime2,@P3 datetime2,@P4 int,@P5 nvarchar(4000),@P6 nvarchar(4000),@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 nvarchar(4000),@P10 int,@P11 bigint)update UNI_EVT_EXTRACTIONS set DATASOURCE_ID=@P0, LOG_ID=@P1, START_TIMESTAMP=@P2, LAST_UPDATE_TIMESTAMP=@P3, RECORD_COUNT=@P4, STATUS=@P5, EXTRACTION_SET_NAME=@P6, LAST_RECORD_TIMESTAMP=@P7, LAST_AUDIT_TIMESTAMP=@P8, LAST_OEX_TIMESTAMP=@P9, CONTINUOUS=@P10 where EXTRACTION_ID=@P11

 

• process 72 is blocked by process 58. Process 72 SQL is :

(@P0 bigint)select LAST_AUDIT_TIMESTAMP from UNI_EVT_EXTRACTIONS where DATASOURCE_ID=@P0 and STATUS='COMPLETED' order by EXTRACTION_ID desc

Environment

Release : 6.x

Component : DOLLAR UNIVERSE

Sub-Component: Reporter.Server

Cause

Unknown, most likely there should be a problem in the Extraction parallelism mechanism that provokes this deadlock.

For this case, during our investigation it was found that Reporter Database Purge had not been running for a long time so the tables UNI_EVT_LOG and UNI_EVT_EXTRACTIONS had reached more than 700K lines which eventually interfered with the extractions generating a deadlock in MS SQL Server Database.

This could be checked with the command unicheckrep -D 

***********Rows Count per table***********
UNI_EVT_RELAUNCHES: 26447437 records.
UNI_EVT_JOBLOGS: 777485 records.
UNI_EVT_MESSAGES: 0 records.
UNI_EVT_PARAMETERS: 917345 records.
UNI_EVT_VARIABLES: 35391043 records.
UNI_EVT_AUDITTRAIL: 9405224 records.
UNI_EVT_DATASOURCES: 512 records.
UNI_EVT_EXTRACTIONS: 701150 records.
UNI_EVT_INTERVENTIONS: 958207 records.
UNI_EVT_PURGES: 14 records.
UNI_EVT_LOG: 701197 records.
UNI_EVT_REPORTS: 33 records.
UNI_EVT_TECH_REPORTS: 24 records.
UNI_EVT_STATUSES: 11 records.

Resolution

Solution:

Launch a Reporter Purge keeping a reasonable retention of records for Extraction History, like 7 days maximum if you are performing Continuous Extractions every X minutes of all your Dollar Universe Nodes.

Once it's finished, check that the amount of records in tables UNI_EVT_LOG and UNI_EVT_EXTRACTIONS have decreased to reasonable values such as 25000 lines or less.

The issue should not occur anymore, if it does, please open a case with Technical Support.

Additional Information

Workaround:

Restart the Reporter Server ( unistoprep and unistartrep)