MySQL – Handling Concurrent UPDATEs on INNODB Tables

MySQLupdate

I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do not want to wait. As I have a 16 core CPU I would like to UPDATE concurrently.

Currently, as I do UPDATE all rows for each column the whole table is locked and I cannot start a further UPDATE on the same table on another column. Is there a (unsafe) way to do it in parallel? Can I disable locking of the table / rows?

Maybe something equivilent to READ UNCOMMITTED…

I am using MySQL 8.0 with INNODB and I it is a single user system so I do not have to worry about uncommited changes.

Best Answer

Is there a (unsafe) way to do it in parallel?

If there is some mark that a particular record was processed by a particular update (or you can insert a field into table structure for this purposes) you may use parallel chunk updates like

SELECT 1
FROM table
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count
FOR UPDATE;

UPDATE table
SET field = function(parameters)
WHERE mark = 'not processed'
ORDER BY primary_key
LIMIT chunk_count;

Moreover, you can start a lot of parallel updates for the same field.

Related Question