Publishing transaction stuck waiting for deployment sites 9.5

Question

After upgrade from SDL Web 8.5 to Sites 9.5 , while publishing we are getting following Error

 ERROR [ProcessingAkkaSpringContext-akka.actor.default-dispatcher-3] SqlExceptionHelper - The INSERT statement conflicted with the FOREIGN KEY constraint "FK_PIPELINE". The conflict occurred in database "Tridion_Content_staging", table "dbo.EXECUTION", column 'EXECUTION_ID'.

Answer 1

AFAIK - There are a lot of situations that can lead to stale data in State Store DB in your case you have used the same content data store DB for your state store publishing worker transactions. For example connection drop between CM and CD or between Deployer and database during the deployment process, or due to SQL side long-running transaction.

This is something we can't control with our software, and to solve this R&D has implemented a cron job that can be used for scheduled clean up this database. It was introduced as part of hotfix for 8.5 and Sites 9.0: CD_11.0.0.14838 / CD_8.5.0.14838. Please refer to its readme.txt for instructions, for your case, I think you don't need HF it was available by default in Sites 9.5 GA, you can just enable cron job configurations in Sites 9.5 Deployer.

In the past, I have seen the following issues for different customer implementations mainly for Deployer – mass publishing issues with the following errors.

• Out of Memory
• SQL Deadlock
• Deployer – crashed
• Timeout
• Database 100% CPU during the mass publishing
• Race condition IO file exception errors for packages
• State Store DB status out of sync due to intermediately stoping the deployer service

Solution to fix the deployer for mass publishing

Stop Deployer Service and make changes in configurations.

• Use deployer-combined with latest cumulative HF version get in to touch with the support
• Configure JVM Memory and garbage collection settings to be optimal in the bin/start.sh or start.ps1 file In general, I advise a 32GB memory server with 24 GB dedicated to the deployer
• cd_storage_conf.xml changes: Replace in Storage configuration:

<Pool Type="jdbc" Size="30" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" MinEvictableIdleTimeout=“5" ValidationQuery="select 1" TestOnBorrow="true"/>

• deployer_conf.xml changes:

Add property to Storage DataSource section:

    <Property Name="validationQuery" Value="select 1"/>
    <Property Name="MaxRetries"  Value="20"/>>

Add property to Worker’s section:

Set and test for the number of worker threads to vary between 15 -25 until stable publish activities (performance tuning exercises)

• Clear incoming package folder and subfolders if there are any items left by not deleted:

D:/SDLDelivery/Deployer_Live/Incoming

• Increasing the connection pool size from 15 to 40 depends on your high volume requirements (performance fine-tuning exercise), default connection bool is defined as 5

• Clean up State Store by executing the following queries on state store DB, Get in touch with SDL support for the script if there are any records leftover by not deleted, or you could install HF and setup to a scheduled cron job to automatically state.store.cleanup.cron property in application.properties file. TAKE A BACKUP before doing anything

delete from [StateDbName].EXECUTION_PROPERTY;
delete from [StateDbName].STEP;
delete from [StateDbName].PIPELINE;
delete from [StateDbName].EXECUTION;

• Enable Snapshot isolation on state store database only applicable for MSSQL/RDS SQL

ALTER DATABASE [StateDbName] SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [StateDbName] SET READ_COMMITTED_SNAPSHOT ON

I hope it helps