[ZBX-25216] PostgreSQL deadlock while updating template Created: 2024 Sep 11  Updated: 2024 Sep 16

Status: Confirmed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: None
Fix Version/s: None

Type: Problem report Priority: Major
Reporter: Artem Assignee: Zabbix Development Team
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Zabbix server 7.0.1
Zabbix frontend 7.0.0
Postgresql 13.7
DB size 50GB
Elasticsearch 7 as a history storage

OS: Oracle linux 9

Number of hosts (enabled/disabled) 16125 16097 / 28
Number of templates 1615
Number of items (enabled/disabled/not supported) 2253788 2085852 / 104352 / 63584
Number of triggers (enabled/disabled [problem/ok]) 1423413 1320780 / 102633 [24920 / 1295860]


Attachments: PNG File Screenshot from 2024-09-11 14-21-21.png    

 Description   

Steps to reproduce:

  1. Open template, linked to big amount of hosts(all hosts in my case)
  2. Select new template with discovery rule in it
  3. Click "Update" button

Result:

Template update fails with deadlock detected.

See screenshot...

full error text:

 * pg_query(): Query failed: ERROR: deadlock detected DETAIL: Process 110258 waits for AccessExclusiveLock on tuple (4,38) of relation 17433 of database 16387; blocked by process 171456. Process 171456 waits for ShareLock on transaction 948895913; blocked by process 171417. Process 171417 waits for ShareLock on transaction 948890489; blocked by process 110258. HINT: See server log for query details. [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerTemplateUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTemplate->update() → CTemplate->updateForce() → CHostGeneral->updateTemplates() → CHostGeneral::linkTemplatesObjects() → CDiscoveryRule::linkTemplateObjects() → CTriggerGeneral->syncTemplates() → CTriggerGeneral->inherit() → CTriggerGeneral->createReal() → CTriggerGeneral->implode_expressions() → DB::reserveIds() → DBselect() → pg_query() in include/db.inc.php:249]
 * Error in query [SELECT nextid FROM ids WHERE table_name='functions' AND field_name='functionid' FOR UPDATE] [ERROR: deadlock detected DETAIL: Process 110258 waits for AccessExclusiveLock on tuple (4,38) of relation 17433 of database 16387; blocked by process 171456. Process 171456 waits for ShareLock on transaction 948895913; blocked by process 171417. Process 171417 waits for ShareLock on transaction 948890489; blocked by process 110258. HINT: See server log for query details.]
 * pg_query(): Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block [zabbix.php:17 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerTemplateUpdate->doAction() → CApiWrapper->__call() → CFrontendApiWrapper->callMethod() → CApiWrapper->callMethod() → CFrontendApiWrapper->callClientMethod() → CLocalApiClient->callMethod() → CTemplate->update() → CTemplate->updateForce() → CHostGeneral->updateTemplates() → CHostGeneral::linkTemplatesObjects() → CDiscoveryRule::linkTemplateObjects() → CTriggerGeneral->syncTemplates() → CTriggerGeneral->inherit() → CTriggerGeneral->createReal() → CTriggerGeneral->implode_expressions() → DB::reserveIds() → DBexecute() → pg_query() in include/db.inc.php:368]
 * Error in query [SELECT nextid FROM ids WHERE table_name='functions' AND field_name='functionid' FOR UPDATE] [ERROR: current transaction is aborted, commands ignored until end of transaction block]
 * Your database is not working properly. Please wait a few minutes and try to repeat this action. If the problem still persists, please contact system administrator. The problem might be caused by long running transaction or row level lock accomplished by your database management system.

In the postgresql logs I see the same error.

I've  tried for several times with the same result.

 



 Comments   
Comment by Vladislavs Sokurenko [ 2024 Sep 16 ]

There is message "See server log for query details." could you please provide those details if available.

Generated at Mon Aug 04 08:54:53 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.