Zabbix: debugging ‘Lock wait timeout exceeded; try restarting transaction’ on the ‘ids’ table

Mattias Geniar, Friday, April 13, 2012 - last modified: Tuesday, December 16, 2014

I recently had fun troubleshooting an issue on a Zabbix server, where the logs would start to fill up with reports such as these.

...
23966:20120412:234746.094 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid']
23942:20120412:234750.128 slow query: 50.982490 sec, "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'"
zabbix_server [23942]: ERROR [file:db.c,line:1582] Something impossible has just happened.
...
23969:20120412:234752.153 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid']
23969:20120412:234752.153 slow query: 50.206625 sec, "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'"
zabbix_server [23969]: ERROR [file:db.c,line:1582] Something impossible has just happened.

The problem itself is the "Lock wait timeout exceeded; try restarting transaction", which is actually a MySQL problem in this case. It's caused in part by InnoDB row-level locks on the very same row that the DB Syncers in Zabbix are trying to reach.

The query in particular, "update ids set nextid=nextid+1 where nodeid=0 and table_name='events' and field_name='eventid'", is Zabbix's way of insuring a unique ID for each event. Instead of using a custom method per database backend (sequences for PostgreSQL or auto_increment in MySQL), this implementation was chosing. In race conditions where a lot of events are fired at once, this causes quite long row-level locks on the very same row ('events') making the DBSyncer processes or others re-try the transaction over and over again, never completing.

A sort of workaround was to transform our database from a (relatively) slow innodb to a (relatively) faster MyISAM for the 'ids' table. Seeing as this table usually only contains around 40 rows, that are frequently updated, this seemed like a good solution.

mysql> ALTER TABLE ids ENGINE = MyISAM;
Query OK, 44 rows affected (0.06 sec)
Records: 44  Duplicates: 0  Warnings: 0

I believe a long-term solution would be for Zabbix Server to have knowledge of which database backend is being used (MySQL/PostgreSQL/...) and use the ID-incrementer provided by that database itself, instead of using a generic workaround such as this.



Hi! My name is Mattias Geniar. 👋 I'm an independent software developer ⌨️ & Linux sysadmin 👨‍💻, a general web geek & public speaker. Currently working on DNS Spy & Oh Dear! Follow me on Twitter as @mattiasgeniar 🐦.

🔥 If you're stuck with a technical problem, I'm available for hire to help you fix it!

Share this post

Did you like this post? Help me share it on social media! Thanks. 🤗

Have feedback?

New comments have been disabled on this blog, existing comments will remain as-is. Want to give feedback? Is there a mistake in the post?

Send me a tweet on @mattiasgeniar!

Comments

chris Tuesday, September 25, 2012 at 13:35 -

Thanks Mattias. It really helped me ! I was facing exactly the same issue.


V.Reddy Thursday, February 12, 2015 at 20:45 -

Hi,
I have same issue in my zabbix server.
17665:20150212:142536.984 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [insert into history (itemid,clock,ns,value)

please will you help me what is exact sql query i need to execute?


John Sunday, April 10, 2016 at 19:33 -

Strange as it seems it hasn’t been fixed in all this time.
We are on Zbx 2.4 and still getting these errors. Trying all these different solutions.