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.
Our DBAs have analyzed the issue and found that this is a lock cycle.
Here's what they told us:
SELECT COUNT(extraction_id) FROM [DU_Reporter].[dbo].[UNI_EVT_EXTRACTIONS] WHERE STATUS = 'COMPLETED' AND LAST_UPDATE_TIMESTAMP > DATEADD(mi,-10,GETDATE())
(@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
(@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 [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]
(@P0 bigint)select LAST_AUDIT_TIMESTAMP from UNI_EVT_EXTRACTIONS where [email protected] and STATUS='COMPLETED' order by EXTRACTION_ID desc
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.
Release : 6.x
Component : DOLLAR UNIVERSE
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.
Restart the Reporter Server ( unistoprep and unistartrep)