Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-6717

Deadlock on profiles table during concurrent executions of DELETE and UPDATE SQL queries due to bad design of SQL queries.

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.6
    • Fix Version/s: None
    • Component/s: Frontend (F)
    • Labels:
    • 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:
      profileid (i.e. PK) values range is cover all the table rows - profileid BETWEEN 000000000000000 AND 099999999999999
      idx2 non unique index also cover full table scan - idx2 BETWEEN 000000000000000 AND 099999999999999
      idx2=46 is filter is invalid due to above idx2 column condition

      Dead lock is caused by UPDATE query - all rows lock by by (profileid BETWEEN 000000000000000 AND 099999999999999)
      condition.

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              ejames James Sperry
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: