PostgreSQL ShareLock errors

[itguy12]

I am using Zabbix Server and Agent 1.8.5 with PostgreSQL 8.4.7 backend database. I am trying to mass update dozens of items and encountering a lot of errors. However, sometimes my mass updates work on a set of items and other times it does not -- basically, I can't find a common thread as to why these are occurring. Here are the errors:

zabbix_server.log

ERROR: deadlock detected
DETAIL: Process 11920 waits for ShareLock on transaction 7556107; blocked by process 8618.
Process 8618 waits for ShareLock on transaction 7556087; blocked by process 11920.
Process 11920: UPDATE items SET description='Host local time',key_='system.localtime',hostid=1001000000100 63,delay=600,history=90,type=0,snmp_community='',s nmp_oid='',value_type=3,data_type=0,trapper_hosts= '',snmp_port=161,units='unixtime',multiplier=0,del ta=0,snmpv3_securityname='',snmpv3_securitylevel=0 ,snmpv3_authpassphrase='',snmpv3_privpassphrase='' ,formula='1',trends=365,logtimefmt='',valuemapid=0 ,delay_flex='',params='',ipmi_sensor='',templateid =100100000022845,authtype=0,username='',password=' ',publickey='',privatekey='' WHERE itemid=100100000025701
Process 8618: update items set lastclock=1308685473,prevvalue=lastvalue,prevorgva lue=NULL,lastvalue='99.944857' where itemid=100100000023974;

postgresql.log

pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: deadlock detected DETAIL: Process 32460 waits for ShareLock on transaction 7558466; blocked by process 8618. Process 8618 waits for ShareLock on transaction 7558453; blocked by process 32460. HINT: See server log for query details.[/var/www/html/zabbix/include/db.inc.php:540]
Error in query [UPDATE items SET description='Number of running processes',key_='proc.num[,,run]',hostid=100100000010156,delay=300,history=7,type= 0,snmp_community='',snmp_oid='',value_type=3,data_ type=0,trapper_hosts='',snmp_port=161,units='',mul tiplier=0,delta=0,snmpv3_securityname='',snmpv3_se curitylevel=0,snmpv3_authpassphrase='',snmpv3_priv passphrase='',formula='0',trends=365,logtimefmt='' ,valuemapid=0,delay_flex='',params='',ipmi_sensor= '',templateid=100100000022857,authtype=0,username= '',password='',publickey='',privatekey='' WHERE itemid=100100000030427] [ERROR: deadlock detected DETAIL: Process 32460 waits for ShareLock on transaction 7558466; blocked by process 8618. Process 8618 waits for ShareLock on transaction 7558453; blocked by process 32460. HINT: See server log for query details.]
pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block[/var/www/html/zabbix/include/db.inc.php:439]
Error in query [SELECT * FROM items WHERE (itemid IN (100100000030427)) ] [ERROR: current transaction is aborted, commands ignored until end of transaction block]
pg_fetch_assoc() expects parameter 1 to be resource, boolean given[/var/www/html/zabbix/include/db.inc.php:613]
pg_free_result(): supplied argument is not a valid PostgreSQL result resource[/var/www/html/zabbix/include/db.inc.php:615]

[barbaros]

Hi there,

I have the same problem with similiar configuration (CentOS5+PGSQL8.4.7). The problem exists when using Zabbix Server 1.8.5 and 1.8.7. The problem occurs when i try to mass upadate a lof of items. Somethimes update works OK, somethimes produce lots of errors.

Example errors:

Zabbix Server

pg_query(): Query failed: ERROR: deadlock detected DETAIL: Process 6796 waits for ShareLock on transaction 60194281; blocked by process 24635. Process 24635 waits for ShareLock on transaction 60194221; blocked by process 6796. HINT: See server log for query details. [include/db.inc.php:546]
Error in query [UPDATE items SET description='Used disk space on $1',key_='vfs.fs.size[/var,used]',hostid=10110,delay=300,history=7,type=0,snmp_community='',snmp_oid='',value_type=0,data_type=0,trapper_hosts='',snmp_port=161,units='B',multiplier=0,delta=0,snmpv3_securityname='',snmpv3_securitylevel=0,snmpv3_authpassphrase='',snmpv3_privpassphrase='',formula='1024',trends=365,logtimefmt='',valuemapid=0,delay_flex='',params='',ipmi_sensor='',templateid=22340,authtype=0,username='',password='',publickey='',privatekey='' WHERE itemid=40001] [ERROR: deadlock detected DETAIL: Process 6796 waits for ShareLock on transaction 60194281; blocked by process 24635. Process 24635 waits for ShareLock on transaction 60194221; blocked by process 6796. HINT: See server log for query details.]
pg_query(): Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block [include/db.inc.php:445]
Error in query [SELECT * FROM items WHERE (itemid IN (40001)) ] [ERROR: current transaction is aborted, commands ignored until end of transaction block]
pg_fetch_assoc() expects parameter 1 to be resource, boolean given [include/db.inc.php:619]
pg_free_result() expects parameter 1 to be resource, boolean given [include/db.inc.php:621]
pg_query(): Query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block [include/db.inc.php:445]
Error in query [SELECT itemid,description,key_,hostid,delay,history,status,type,snmp_community,snmp_oid,value_type,data_type,trapper_hosts,snmp_port,units,multiplier,delta,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authpassphrase,snmpv3_privpassphrase,formula,trends,logtimefmt,valuemapid,delay_flex,params,ipmi_sensor,templateid,authtype,username,password,publickey,privatekey FROM items WHERE itemid=22538] [ERROR: current transaction is aborted, commands ignored until end of transaction block]
pg_fetch_assoc() expects parameter 1 to be resource, boolean given [include/db.inc.php:619]
pg_free_result() expects parameter 1 to be resource, boolean given [include/db.inc.php:621]
No item with itemid=[22538]

PostgreSQL Server

LOG:  could not receive data from client: Connection reset by peer
LOG:  unexpected EOF on client connection
ERROR:  deadlock detected
DETAIL:  Process 6796 waits for ShareLock on transaction 60194281; blocked by process 24635.
        Process 24635 waits for ShareLock on transaction 60194221; blocked by process 6796.
        Process 6796: UPDATE items  SET description='Used disk space on $1',key_='vfs.fs.size[/var,used]',hostid=10110,delay=300,history=7,type=0,snmp_community='',snmp_oid='',value_type=0,data_type=0,trapper_hosts='',snmp_port=161,units='B',multiplier=0,delta=0,snmpv3_securityname='',snmpv3_securitylevel=0,snmpv3_authpassphrase='',snmpv3_privpassphrase='',formula='1024',trends=365,logtimefmt='',valuemapid=0,delay_flex='',params='',ipmi_sensor='',templateid=22340,authtype=0,username='',password='',publickey='',privatekey='' WHERE itemid=40001
        Process 24635: update items set lastclock=1317714343,prevvalue=lastvalue,lastvalue='21521833984' where itemid=23263;
        update items set lastclock=1317714343,prevorgvalue='82253',prevvalue=lastvalue,lastvalue='0' where itemid=25841;
        update items set lastclock=1317714343,prevorgvalue='433334',prevvalue=lastvalue,lastvalue='0' where itemid=25842;
        update items set lastclock=1317714343,prevorgvalue='0',prevvalue=lastvalue,lastvalue='0' where itemid=27403;
        update items set lastclock=1317714343,prevorgvalue='0',prevvalue=lastvalue,lastvalue='0' where itemid=28003;
        update items set lastclock=1317714343,prevvalue=lastvalue,lastvalue='2' where itemid=30401;
        update items set lastclock=1317714343,prevvalue=lastvalue,lastvalue='0:26:cb:b2:9d:40' where itemid=30463;
        update items set lastclock=1317714343,prevvalue=lastvalue,lastvalue='1000000000' where itemid=30642;
        update items set lastclock=1317714343,prevvalue=lastvalue,lastvalue='33.000000' where itemid=35982;
        update items set lastclock=1317714343,prevorgvalue='125005471',prevvalue=las
HINT:  See server log for query details.
STATEMENT:  UPDATE items  SET description='Used disk space on $1',key_='vfs.fs.size[/var,used]',hostid=10110,delay=300,history=7,type=0,snmp_community='',snmp_oid='',value_type=0,data_type=0,trapper_hosts='',snmp_port=161,units='B',multiplier=0,delta=0,snmpv3_securityname='',snmpv3_securitylevel=0,snmpv3_authpassphrase='',snmpv3_privpassphrase='',formula='1024',trends=365,logtimefmt='',valuemapid=0,delay_flex='',params='',ipmi_sensor='',templateid=22340,authtype=0,username='',password='',publickey='',privatekey='' WHERE itemid=40001
ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT * FROM items WHERE  (itemid IN (40001))
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Any ideas?

[tvtue]

Hi,

I have a very similar error. Have you solved this yet? And if yes, what was the solution?

I am running Zabbix 2.0.3 with postgresql-9.1 on Ubuntu 12.04.1 LTS


TIA

Timo

[barbaros]

Hi,

I haven't find any solution and help with this case. After disabling housekeer thing are pretty much better. I am planning migration to MySQL next week.

[tvtue]

Hello barbaros,

thank you for your reply. I am still hoping that I can find another solution, because I don't want to change everything to MySQL.

Timo

[barbaros]

Hi. Maybe you should consider disabling zabbix housekeeper and enabling table partitioning on your database server?

[tvtue]

Yes, that is something I am considering anyway. But I am not sure that disabling the housekeeper also solves the ShareLock errors.

[barbaros]

I doesn't solve the problem but in my opinion, without housekeeper frequency of sharelocks is much lower.

[tvtue]

Ok, thank you!