MySQL UPDATE左连接超时，批量迁移数据有没有更优解决方案？

问题场景 #

需要将25000条记录从旧表GW_xImage迁移到字段名不同的新表images，最初使用关联UPDATE语句执行时出现超时，哪怕仅更新2个字段，也会抛出#1205 - Lock wait timeout exceeded; try restarting transaction错误。

原超时执行语句如下：

UPDATE images AS A LEFT JOIN GW_xImage AS B on A.id=B.Image_ID 
SET 
A.created_at = B.Date_Created,
A.updated_at = B.Date_Modified,
A.is_active = B.Mark_Active,
... 后续还有30个字段赋值逻辑;

高可用解决方案 #

使用INSERT ... SELECT语法直接批量写入，执行速度远高于关联更新，不会触发锁超时问题，语句如下：

INSERT INTO images (id, created_at, updated_at, is_active, is_image,
    is_private, is_default, rating, code, number, subfolder,
    title, caption, summary, location, subregion, region,
    country_id, gps, altitude, keywords, author, note, date,
    month, year, type, format, ratio, width, height, camera_body,
    camera_lens, camera_focal, camera_iso, camera_speed, camera_aperture)
SELECT Image_ID, Date_Created, Date_Modified, Mark_Active, Mark_IsImage,
    Mark_Private, Mark_Default, TotalStars, ShortCodeOrgan, Image_Number, Image_SubFolder,
    ImageTitle, Image, ImageNewTitle, Location, SubRegion, Region,
    Country_ID, LocationGPS, LocationAltitude, Keywords, Author, Note, Image_Date,
    Image_Month, Image_Year, Image_Type, Image_Format, Image_Ratio, Image_MaxWPx, Image_MaxHPx, Camera_Body,
    Camera_Lens, Camera_Focale, Camera_ISO, Camera_Speed,
    Camera_Aperture
FROM GW_xImage;

内容的提问来源于stack exchange，提问作者Paul Godard