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

Question

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.

Answer 1

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: Index Cookbook

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