MySQL dead-locking with UPDATE JOIN and INSERT SELECT statements 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 &lt; :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?
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 ...