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?
On AWS RDS, I had a DB instance fill up, which caused this error on the client.