org.hibernate.StaleStateException in deployer log, incoming folder not cleaned up

Question

Our Tridion 2013 SP1 setup, has a deployer which deploys to a broker database. We often get failed and throttled publish transactions. We did some configuration changes with workers et cetera.

Tonight we had no failed transactions, but when I checked the incoming folder of our deployer, I saw like 20 files, which were not cleand up (and this will cause throttled statuses after a while).

In the deployer log i saw the following for one of those transactions:

2017-10-24 08:03:43,320 WARN PreCommitPhase - Failed to Prepare: tcm:0-744408-66560 error: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

This transactions failed 3 times, and after the 4th retry, it succeeded, but the package wasn't removed from the incoming folder.

Question 1: What is causing this error?

Question 2: Why is the incoming folder not cleaned up after the transaction succeeded after a few retries?

ps. cleanup is set to true in deployer config, packages that succeed the first try are cleand up.

Answer 1

I think this hotfix should help CD_2013.1.1.147 which addresses issue which you are describing. It is mentioned as CRQ-147 in the hotfix details page http://csei.sdl.com/hotfixes/#/hotfixes/c

Search for CD_2013.1.1.147 on the above link and try applying this hotfix.

Answer 2

Issue is solved! We discovered a deadlock on the brokerdatabase (at the same time the incoming folder was 'full'), caused by the following query:

delete from REFERENCE_ENTRIES where PUBLICATION_ID=@P0 and REFERENCED_URI=@P1

At that time 8 queries were being blocked, like the following:

update TAXFACETS delete from TAXFACETS where PUBLICATION_ID=@P0 and TAXONOMY_ID=@P1 [truncated]

SDL Support suggested the following action:

https://gateway.sdl.com/apex/communityknowledge?articleName=000003519

This seemed to be the solution for this issue. Thank you all for your help.