MySQL错误代码:1205.使用内部联接更新期间锁定等待超时 [英] MySQL Error Code: 1205. Lock wait timeout during update with inner join

问题描述

我试图更新表simple_pack_data中的Time_Stamp字段，以匹配temp_data表中标题相似的字段中的值.每个表都有名为Test_Number和Time_Marker的字段，我正在使用这些字段来INNER JOIN表. Time_Marker就像一个读数计数，其中Time_Stamp是从测试开始的实际时间.

I am trying to update the Time_Stamp field in my table, simple_pack_data, to match the values in the similarly titled field in my temp_data table. The tables each have fields called Test_Number and Time_Marker, which I'm using to INNER JOIN the tables. Time_Marker is like a reading count, where Time_Stamp is an actual time from the start of the test.

我想一次更新Time_Stamp个测试，所以我一直在尝试的代码是:

I want to update the Time_Stamp one test at a time, so the code I have been trying is:

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";

运行此命令需要50秒钟以上，并且出现1205错误.如果我运行结构类似的select语句:

When I run this it takes over 50 seconds and I get the 1205 error. If I run a similarly structured select statement:

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";

花费的时间不到一秒钟，我知道join可以正常工作.更新真的花了这么长时间吗?如果是这样，是否有任何方法可以更改超时值，以便它可以通过超时值?

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?

推荐答案

此错误完全是MySQL无法正常执行的.最好的解决方案是摆脱MySQL，但缺乏这种能力，此性能博客帖子过去帮助我解决了这个问题.

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 post has helped me get around this in the past.

MySQL有很多这些小陷阱.就像在Access中工作一样，程序有一半的时间会做错事而不引发错误.

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错误代码:1205.使用内部联接更新期间锁定等待超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！