mysql - REPLACE INTO 导致超过锁定等待超时；尝试重启交易

Question

我正在尝试使用此查询将数据从一个列复制到另一个表中的另一列

REPLACE INTO am_finder_value (dropdown_id, name) SELECT 6, ukyear FROM am_finder_models WHERE ukyear IS NOT NULL AND ukyear <>'';

am_finder_models

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| model_id   | text    | YES  |     | NULL    |                |
| modelname  | text    | YES  |     | NULL    |                |
| ukyear     | text    | YES  |     | NULL    |                |
| euroyear   | text    | YES  |     | NULL    |                |
| chassis_id | text    | YES  |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

am_finder_value

+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| value_id    | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
| parent_id   | int(10) unsigned      | NO   | MUL | NULL    |                |
| dropdown_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| name        | varchar(255)          | NO   |     | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+

我不断得到

Lock wait timeout exceeded; try restarting transaction

我已将超时增加到 300

| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 300   |
+--------------------------+-------+

有没有更好更有效的方式来复制数据？

Answer 1

使用

SHOW PROCESSLIST;

在 phpmyadmin 中这将显示具有 ID 和执行时间的线程这样您就可以杀死执行时间过长的线程