[ZBX-6717] Deadlock on profiles table during concurrent executions of DELETE and UPDATE SQL queries due to bad design of SQL queries. Created: 2013 Jun 17 Updated: 2017 May 30 Resolved: 2016 Jan 25 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Frontend (F) |
Affects Version/s: | 2.0.6 |
Fix Version/s: | None |
Type: | Incident report | Priority: | Minor |
Reporter: | James Sperry | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 0 |
Labels: | deadlock, dm, mysql | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
MySQL Backend |
Description |
Almost everyday, we notice dead lock on the database due to these SQL queries. Locking entire table for single or few rows operation also causing slow running other SQL queries. Deadlock on profiles table during concurrent executions of DELETE and UPDATE SQL queries due to bad design of SQL queries. UPDATE profiles SET value_str='20120518211248', type=3 WHERE userid=13 AND idx='web.screens.stime' AND profileid BETWEEN 000000000000000 AND 099999999999999 AND idx2=46 AND idx2 BETWEEN 000000000000000 AND 099999999999999 The following issues with above SQL query design causing always FULL TABLE scan: Dead lock is caused by UPDATE query - all rows lock by by (profileid BETWEEN 000000000000000 AND 099999999999999) To avoid dead lock or longer locks on table rows, the modified UPDATE query should be like UPDATE profiles SET value_str='20120518211248', type=3 WHERE userid=13 AND idx='web.screens.stime' AND idx2=46; Please fix the SQL query as suggested. Thanks |
Comments |
Comment by Alexei Vladishev [ 2013 Jul 17 ] |
I believe it is already fixed under |
Comment by Aleksandrs Saveljevs [ 2016 Jan 25 ] |
This seems to be fixed already in Zabbix 2.4, perhaps as a side effect of removing distributed monitoring. Exact fix version is unknown. |