Symptoms
Various issues can be observed in POA/PBA-E due to database deadlocks.
In postgresql log the following errors may be observed:
ERROR: deadlock detected
In BM.log such messages may be logged:
... Database deadlock detected while executing ' SELECT ... FOR UPDATE'..
In particular, in PBA-E an order may fail with error:
Parallels Operations Automation error #extype_id #100, module_id #db_service, Database deadlock detected while executing ' SELECT hosting_id FROM cqmail_hostings WHERE hosting_rti_id ? AND state ! ? FOR UPDATE'
In poa.debug.log you will see similar entries:
INF [SYSTEM 1:5885:b1053ba0 OpenAPI]: <<<== Method 'pem.setResourceTypeLimits' is executed, with status: { 'error_message' => 'Database deadlock detected while executing ' SELECT hosting_id FROM cqmail_hostings WHERE hosting_rti_id = ? AND state != ? FOR UPDATE'.', 'extype_id' => 100, 'module_id' => 'db_service', 'properties' => { 'action' => ' SELECT hosting_id FROM cqmail_hostings WHERE hosting_rti_id = ? AND state != ? FOR UPDATE', 'reason' => 'ERROR: deadlock detected;
Cause
If POA version is older than 5.4.14, the problem may be caused by internal issue with ID POA-78210.
Resolution
Install POA 5.4 Update 14
If orders fail in PBA-E as in example above, the following can be done before POA is updated:
Check that postgres has deadlock (after some period of time it could be resolved by system)
plesk=> SELECT bl.pid AS blocked_pid, a.usename AS blocked_user,kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS blocked_statement FROM pg_catalog.pg_locks bl JOIN pg_catalog.pg_stat_activity a ON bl.pid = a.procpid JOIN pg_catalog.pg_locks kl JOIN pg_catalog.pg_stat_activity ka ON kl.pid = ka.procpid ON bl.transactionid = kl.transactionid AND bl.pid != kl.pid WHERE NOT bl.granted;
If you see nothing the failed order could be resubmitted for provisioning, it will be completed.
If deadlock exists, restart of postgresql and poa (after postgresql gets back online) will be required.