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
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
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?