Improving a MySQL query that result in a Lock wait timeout exceeded; try restarting transaction



  • I have a table that tracks the action of my users (used for logging and debugging for their purpose).

    From time to time, I need to mark the data as treated by doing the following call :

    UPDATE actions
    SET is_treated = 1
    WHERE code = 200
        AND is_treated = 0
        AND account_uuid = :uuid
        AND DATE(executed) = UTC_DATE()
    LIMIT 1000
    

    I do that command inside a loop that loop as long as there are "is_treated" at 0 for that day. I limited to 1000 to fasten the query.

    That table currently have around 1M entries.

    Despite setting the limit at 1000, the query fails from time to time with...

    Lock wait timeout exceeded; try restarting transaction

    I tried reducing the limit to 100, with the same error.

    For information, the table as the following indexes :

    $> SHOW INDEX FROM actions;
    

    +-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | actions | 0 | PRIMARY | 1 | id | A | 952794 | NULL | NULL | | BTREE | | |
    | actions | 1 | account_uuid | 1 | account_uuid | A | 696 | NULL | NULL | YES | BTREE | | |
    | actions | 1 | api_token | 1 | api_token | A | 848 | NULL | NULL | YES | BTREE | | |
    | actions | 1 | ix_actions_code | 1 | code | A | 6 | NULL | NULL | | BTREE | | |
    | actions | 1 | ix_actions_executed | 1 | executed | A | 952794 | NULL | NULL | | BTREE | | |
    | actions | 1 | ix_actions_is_treated | 1 | is_treated | A | 2 | NULL | NULL | | BTREE | | |
    +-------------+------------+---------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    Is there a way to optimize the query to be run processed faster ?

    I was wondering if doing something like the follow would not be faster.

    UPDATE actions
    SET is_treated = 1
    WHERE id IN
    (
        SELECT id
        FROM actions
        WHERE code = 200
            AND is_treated = 0
            AND account_uuid = :uuid
            AND DATE(executed) = UTC_DATE()
        LIMIT 1000
    );
    

    What do you recommend?

    Thank you in advance.



  • Sargable

    If the datatype of executed is DATE, then remove the function call. If it is TIMESTAMP or DATETIME, then change the test to:

        executed >= UTC_DATE()
    AND executed  < UTC_DATE() + INTERVAL 1 DAY
    

    In addition, you need this composite index:

    INDEX(account_uuid, code,  -- in any order
          executed)            -- last
    

    This index should significantly speed up the UPDATE.

    (I am guessing that only one row is affected??)

    1000

    Are you looping through a lot of rows, doing 1000 at a time? How long is each iteration taking? Decrease the "1000" so that it does takes only a few seconds.

    Building good index: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

    In almost all cases, MySQL will use only one index in one [sub]query.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2