postgresql: deadlock detected

[svrkispm]

hello,
we are using zabbix server v. 1.4.5 on gentoo with db postgresql 8.2.6. This is the second time i got the following:

 29681:20080611:100603 Query::update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'
 29681:20080611:100603 Query failed:PGRES_FATAL_ERROR:ERROR:  deadlock detected
DETAIL:  Process 29725 waits for ShareLock on transaction 163509800; blocked by
process 29731.
Process 29731 waits for ShareLock on transaction 163509806; blocked by process 29725.

 29681:20080611:100603 Query::select nextid from ids where nodeid=0 and table_name='events' and field_name='eventid'
 29681:20080611:100603 Query failed:PGRES_FATAL_ERROR:ERROR:  current transaction is aborted, commands ignored until end of transaction block

 29670:20080611:100603 One child process died. Exiting ...
 29670:20080611:100605 ZABBIX Server stopped

Now, when i try to start the server, i get this:

  5980:20080613:083621 Starting zabbix_server. ZABBIX 1.4.5.
  5980:20080613:083621 **** Enabled features ****
  5980:20080613:083621 SNMP monitoring:       YES
  5980:20080613:083621 WEB monitoring:        YES
  5980:20080613:083621 Jabber notifications:  YES
  5980:20080613:083621 IPv6 support:           NO
  5980:20080613:083621 **************************
  6005:20080613:083626 server #7 started [Trapper]
  6007:20080613:083626 server #8 started [Trapper]
  6009:20080613:083626 server #9 started [Trapper]
  5999:20080613:083626 server #1 started [Poller. SNMP:ON]
  6011:20080613:083626 server #10 started [Trapper]
  6014:20080613:083626 server #11 started [Trapper]
  6001:20080613:083626 server #3 started [Poller. SNMP:ON]
  6016:20080613:083626 server #12 started [ICMP pinger]
  6018:20080613:083626 server #13 started [Alerter]
  6021:20080613:083626 server #14 started [Housekeeper]
  6021:20080613:083626 Executing housekeeper
  6023:20080613:083626 server #15 started [Timer]
  6026:20080613:083626 server #17 started [Node watcher. Node ID:0]
  6028:20080613:083626 server #18 started [HTTP Poller]
  6030:20080613:083626 server #19 started [HTTP Poller]
  6032:20080613:083626 server #20 started [HTTP Poller]
  6036:20080613:083626 server #21 started [HTTP Poller]
  6038:20080613:083626 server #22 started [HTTP Poller]
  5980:20080613:083626 server #0 started [Watchdog]
  6000:20080613:083626 server #2 started [Poller. SNMP:ON]
  6004:20080613:083626 server #6 started [Poller. SNMP:ON]
  6002:20080613:083626 server #4 started [Poller. SNMP:ON]
  6003:20080613:083626 server #5 started [Poller. SNMP:ON]
  6040:20080613:083626 server #23 started [Discoverer. SNMP:ON]
  6018:20080613:083626 JABBER: disconnecting
  6025:20080613:083626 server #16 started [Poller for unreachable hosts. SNMP:ON]
  6025:20080613:083627 Enabling host [tesco-production]
  6003:20080613:083638 Query::update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'
  6003:20080613:083638 Query failed:PGRES_FATAL_ERROR:ERROR:  deadlock detected
DETAIL:  Process 6045 waits for ShareLock on transaction 163615771; blocked by process 6017.
Process 6017 waits for ShareLock on transaction 163615773; blocked by process 6045.

  6003:20080613:083638 Query::select nextid from ids where nodeid=0 and table_name='events' and field_name='eventid'
  6003:20080613:083638 Query failed:PGRES_FATAL_ERROR:ERROR:  current transaction is aborted, commands ignored until end of transaction block

  5980:20080613:083638 One child process died. Exiting ...
  5980:20080613:083640 ZABBIX Server stopped

Last time this happended i exported configuration, wiped the entire db, reimported it back. Now when the same thing has happened, i would like to solve this before using zabbix again.
Thanx for any advice.

[vinny]

Hi,
the error is in the log : deadlock between processes 6045 & 6017.
See if the process are still connected to the DB ?

If not, u can do nothing else but restarting zabbix.

vinny

[svrkispm]

i don't see any connection when i look into pg_stats tables. the process crashes as soon as it gets deadlock from postgresql and that causes the whole server to shut down.
so whenever i restart the server the same happens - 2 of server processes go into deadlock, then they crash and then the whole server crashes just after it has started.
imo the only solution to this is some change in database, but i have no idea where or what.

[svrkispm]

i gave up. i installed mysql. everything fine so far. anyway, i still would like to know the solution if theres any. I still have DB dump.