Trivial ids table delete/insert deadlock can occur using ZABBIX API multiple calls with MySQL.
Reproduce - When multiple 10 threads API call - 'trigger.update'
TRANSCTIONS_FAILED and Query:
DELETE FROM ids WHERE nodeid=0 AND table_name='functions' AND field_name='functionid'
Error in MySQL :
"Deadlock found when trying to get lock; try restarting transaction"
Stack trace:
#0 /usr/share/zabbix/include/classes/db/DB.php(145): DB::exception(1, \'DBEXECUTE_ERROR\')
#1 /usr/share/zabbix/include/classes/db/DB.php(117): DB::refreshIds(\'functions\', 1)
#2 /usr/share/zabbix/include/classes/db/DB.php(391): DB::reserveIds(\'functions\', 1)
#3 /usr/share/zabbix/include/triggers.inc.php(978): DB::insert(\'functions\', Array)
#4 /usr/share/zabbix/api/classes/CTrigger.php(1562): implode_exp(\'{TEST:agent.pin...\', \'13521\', Array)
#5 /usr/share/zabbix/api/classes/CTrigger.php(1182): CTrigger->updateReal(Array)\n#6 [internal function]: CTrigger->update(Array)
#7 /usr/share/zabbix/api/rpc/class.czbxrpc.php(120): call_user_func(Array, Array)
#8 /usr/share/zabbix/api/rpc/class.czbxrpc.php(72): czbxrpc::callAPI(\'trigger.update\', Array)
#9 /usr/share/zabbix/api/rpc/class.cjsonrpc.php(71): czbxrpc::call(\'trigger.update\', Array, \'5d063184cb7a800...\')
#10 /usr/share/zabbix/api_jsonrpc.php(54): CJSONrpc->execute()
API Response (ERROR):
"error":{"code":-32602,"message":"Invalid params.", "data":"Cannot implode expression \"{TEST:cpuUsage.count(#2,50,\"gt\")}=1\". DBEXECUTE_ERROR"
Related issue:
ZBX-11061 - Need to exception 'FOR UPDATE' query for API error handling.