2

We've run into a considerable number of dead-locks recently and they seem to be happening at this query:

  INSERT 
    INTO link_click (link_id, created_at, weight)
  SELECT :link_id, :created_at, lw.weight
    FROM link_weight lw
   WHERE lw.link_id = :link_id AND lw.start_at < :created_at
ORDER BY lw.start_at DESC
   LIMIT 1

Where link_id and created_at are passed in as PDO parameters This is part of a larger transaction.

The dead-locks only appear to occur when the following separate query is running on a cron every 30 minutes:

UPDATE owner o
  JOIN (
    SELECT l.owner_id, 
           SUM((lc.state = 1) * lc.weight) total_pending,
           SUM((lc.state = 2) * lc.weight) total_confirmed,
           SUM((lc.state = 3) * lc.weight) total_awaiting_processing,
           SUM((lc.state = 4) * lc.weight) total_processed,
           SUM((lc.state = 5) * lc.weight) total_invalid 
      FROM link_click lc 
      JOIN link l 
        ON l.id = lc.link_id 
     WHERE (:owner_id IS NULL OR l.owner_id = :owner_id)
       AND lc.state != 0
  GROUP BY l.owner_id
       ) raw
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid

Where owner_id is passed in. This is a one shot query not in a transaction but takes roughly 30s to complete.

I wasn't aware that INSERT SELECT or UPDATE JOIN would lock any rows beyond themselves and cause such a dead-lock.

I guess my question is whether separating out the SELECTs (I don't need a FOR UPDATE lock) from each query would help this situation?

Or is it more likely that the dead-lock is coming from the first query's transaction and the second query is just slowing down the database and that's making the dead-locks more frequent?

2

Add these indexes:

link_weight:  (link_id, start_at, weight) -- in this order
link:         (owner_id, id)              -- in this order
link_click:   (state, owner_id)           -- in this order
owner:        (owner_id, total_processed) -- in either order

If owner has PRIMARY KEY(owner_id) then don't add the owner index above.

The goal is to speed up processing to minimize deadlocks. Since deadlocks probably cannot be completely eliminated, be read to rerun transactions when they do occur.

If that does not suffice, break up the UPDATE into two steps:

CREATE TEMPORARY TABLE raw
      ( INDEX(owner_id, total_processed) )
AS
    SELECT ...;   -- from your existing code
UPDATE owner o
  JOIN raw 
    ON raw.owner_id = o.id
   SET o.total_pending = raw.total_pending,
       o.total_confirmed = raw.total_confirmed,
       o.total_awaiting_processing= raw.total_awaiting_processing,
       o.total_processed= raw.total_processed,
       o.total_invalid = raw.total_invalid;

If you need further help, please provide

EXPLAIN SELECT ...
EXPLAIN UPDATE ...
SHOW CREATE TABLE ...
  • Oops, had a typo in the GROUP BY, link_click doesn't have an owner_id just a link_id (hence the join to the table link). I'm guessing that would change your index suggestion for link_click to (state, link_id). – Arth Jun 22 '18 at 11:40
  • Owner doesn't have an owner_id, just its PK id.. so that's all good. – Arth Jun 22 '18 at 11:43
  • 1
    @arth SELECT @@innodb_adaptive_hash_index to determine if the option is ON. If not, SET GLOBAL innodb_adaptive_hash_index=1; can turn it on dynamically. You may find some relief with this enabled. If so, add/change in your my.cnf-ini. Check my profile for contact information, please. – Wilson Hauck Jun 23 '18 at 19:03
  • 1
    TY TY! Adding the the covering index for link_weight saved about 50% of the time on the INSERT SELECT select. Adding another covering index (state, owner_id, weight) index for link_click saved about 80% on the UPDATE JOIN select. Since making this change I've had no deadlocks! Pretty sure all the indexes in MySQL InnoDB are tied to the PK so I didn't bother with the link one as I already have owner_id as an FK. – Arth Jun 25 '18 at 15:00
  • 1
    (And that's one reason I asked for SHOW CREATE TABLE.) – Rick James Jun 26 '18 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.