MySQL 错误代码：1205。使用内连接更新期间锁定等待超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19458960/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error Code: 1205. Lock wait timeout during update with inner join
提问by eh_whatever
I am trying to update the Time_Stampfield in my table, simple_pack_data, to match the values in the similarly titled field in my temp_datatable. The tables each have fields called Test_Numberand Time_Marker, which I'm using to INNER JOINthe tables. Time_Markeris like a reading count, where Time_Stampis an actual time from the start of the test.
我正在尝试更新Time_Stamp表中的字段simple_pack_data，以匹配表中类似标题字段中的值temp_data。每个表都有名为Test_Numberand 的字段Time_Marker，我正在使用这些字段INNER JOIN。Time_Marker就像一个阅读计数，Time_Stamp从测试开始的实际时间。
I want to update the Time_Stampone test at a time, so the code I have been trying is:
我想一次更新Time_Stamp一个测试，所以我一直在尝试的代码是：
UPDATE simple_pack_data s
INNER JOIN (
SELECT *
FROM temp_data t
WHERE t.Test = "3"
) AS tmp
ON s.Test_Number = tmp.Test_Number AND s.Time_Marker = tmp.Time_Marker
SET s.Time_Stamp = tmp.Time_Stamp
WHERE s.Test_Number = "3";
When I run this it takes over 50 seconds and I get the 1205 error. If I run a similarly structured select statement:
当我运行它时，它需要超过 50 秒，并且出现 1205 错误。如果我运行一个类似结构的 select 语句：
SELECT *
FROM simple_pack_data s
INNER JOIN (
SELECT *
FROM temp_data t
WHERE t.Test = "3"
) AS tmp
ON s.Test_Number = tmp.Test AND s.Time_Marker = tmp.Time_Marker
WHERE s.Test_Number = "3";
It takes much less than a second and I know join is working fine. Is the update really taking that long? If so, is there any way to change the timeout value so it can get through it?
只需不到一秒钟，我知道 join 工作正常。更新真的需要那么久吗？如果是这样，有什么方法可以更改超时值，以便它可以通过它？
采纳答案by Twelfth
This error is entirely MySQL not doing as it should. The best solution is to get off of MySQL, but lacking that ability, this performance blog posthas helped me get around this in the past.
这个错误完全是 MySQL 没有做它应该做的。最好的解决方案是摆脱 MySQL，但缺乏这种能力，这篇性能博客文章过去帮助我解决了这个问题。
MySQL has a lot of these little gotchas. It's like working in Access, half the time the program is going to do the wrong thing and not raise an error.
MySQL 有很多这样的小问题。这就像在 Access 中工作一样，有一半的时间程序会做错事，而不是引发错误。
