MySQL 5.7: implementing locking behavior during INSERT with SELECT from a highly concurrent table
I'm trying to figure out I'm working with an unexpected locking behavior in MySQL 5.7 when trying to perform an `INSERT ... SELECT` operation on a table that is under heavy concurrent access. My query looks like this: ```sql INSERT INTO target_table (id, name) SELECT id, name FROM source_table WHERE created_at > NOW() - INTERVAL 1 DAY; ``` What I've noticed is that during peak hours, this query occasionally hangs and doesn't complete, while other `SELECT` queries run against the `source_table` seem to execute fine. The `target_table` has a few indexes, but the `source_table` is heavily indexed and frequently updated. I've tried running the query with `LOW_PRIORITY` to see if it helps with the locking, but it didn't make a difference. Additionally, I checked the `innodb_lock_wait_timeout` setting and it’s set to the default of 50 seconds, which seems reasonable. When the query hangs, I can see in the process list that it’s waiting for a lock on the `source_table`, but there are no long-running transactions that I can identify. I executed the query with `EXPLAIN` but didn’t see anything that stood out as problematic. Here’s the output: ```sql EXPLAIN INSERT INTO target_table (id, name) SELECT id, name FROM source_table WHERE created_at > NOW() - INTERVAL 1 DAY; ``` I'm using InnoDB for storage, and I suspect that the row-level locking could be causing issues when multiple sessions are trying to read and write concurrently. Any insights into what might be causing this locking behavior, or suggestions on how I can optimize this query to avoid deadlocks? Are there specific best practices for managing concurrent access in such scenarios? This is part of a larger web app I'm building.