What is a good practice for large data volume update queries to avoid lock wait timeouts?

Question

So basically, I currently have this query:

UPDATE act AS a
INNER JOIN blok AS b
ON b.fav_pat = a.pat_id
SET a.blok_id = b.id

This is currently timed out due to the large amount of data I have. Is there a way to avoid timeouts without modifying the database configuration?

Answer 1

The flyway package you use works best to allow full rollback of any incomplete operations using the transaction semantics of the host RDBMS. This means that it is designed to perform update operations like the one you showed us in a single ACID-compliant transaction.

If the tables involved are large (millions of rows or more), the transaction can be very large. They can crash your MySQL server, spilling transaction logs to disk or SSD. Committing these transaction logs can take a long time. You didn't mention the number of rows, but if the number of rows is large, Flyway may not be the right tool for the job.

Your lock timeout implies that you are doing this against a database that has other concurrent activity. You may want to do this on a quiet database for best results.

You can increase the lock wait timeout by doing this.

show variables like 'innodb_lock_wait_timeout'; -- previous vale
SET GLOBAL innodb_lock_wait_timeout = 300; -- five min

Then, maybe try again before sunrise on a holiday or at another quiet time. More information here.

When your Flyway job completes, consider restoring the lock timeout to its previous value.

You can also consider batch updates, such as updating 1000 rows at a time. But flyway doesn't seem to support this. If you go this route, you can ask another question.