Article ID: 117637, created on Oct 1, 2013, last review on May 3, 2014

  • Applies to:
  • Operations Automation 5.4

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.

Search Words

database deadlock

cqmail_hostings

pem.setResourceTypeLimits

ac82ce33439a9c1feec4ff4f2f638899 caea8340e2d186a540518d08602aa065 5356b422f65bdad1c3e9edca5d74a1ae 2554725ed606193dd9bbce21365bed4e e12cea1d47a3125d335d68e6d4e15e07

Email subscription for changes to this article
Save as PDF