MySQL Amazon RDS: Lock Wait timeout exceeded

Question

On Mysql (Amazon RDS), when I try to run the following SQL query

UPDATE
    table1 INNER JOIN table2 USING (CommonColumn)
SET
    table1.col1 = table2.x,
    table1.col2 = table2.y

I get this error after around 52 seconds consistently:

Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

How should I resolve this?

table2 has around 17 million records and table2 which is a subset of table1 has 4 million records. Could it be the size of the tables that is the problem or is something wrong with my query?

Answer 1

Try increasing the default number of IO threads if you are using InnoDB engine. RDS defaults to a measly 4. You will need to restart the DB instance for the Parameter Group to use the new values as they are not "dynamic" (require a reboot).

show variables like 'innodb_%io_threads'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | innodb_read_io_threads | 4 | | innodb_write_io_threads | 4 | +-------------------------+-------+