System Cleaner failing to clean pr_sys_context and pr_page_store
The following issues occur on Pega Platform 7.3:
- Socket time-out exception occurs and the SQL in a JAVA method in CleanupExpired fails.
Predictive Diagnostic Cloud (PDC) ID - Socket Time-out (SocketTimeout-75)
- System Cleaner fails to clean pr_sys_context and pr_page_store.
PDC ID- Conflicting Queries (DatabaseDeadlock-179)
PEGA0106 database deadlock detected. User 'pega' PID 13086 blocked by 'pega' PID 15764. Blocked statement was - delete from pegadata.pr_page_store where pxcreatedatetime < $1. Blocking statement was - delete from pegadata.pr_page_store where pyrequestorid in ( select pxrequestorid from pegadata.pr_sys_context where pxCreateDateTime < $1 )
Steps to Reproduce
This is a feature of the database to either wait for a resource to be available or detect the presence of a cycle in the Wait list between two or more transactions that would result in a deadly embrace (never ending wait) unless one of the transactions is terminated.
Here’s the explanation for the reported behavior:
The ClusterAndDBCleaner must be run on only one node, while the NodeCleaner must be run on all the nodes.
The environment in this case runs 24/7 in terms of load. As a result, there is contention on the tables that are used for passivation and a deadlock occurs intermittently.
Ensure that the table content which is older than 7 days is not large.