mysql update 1205,MySQL错误代码：1205。使用内部联接更新期间锁定等待超时

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.

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

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

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?

解决方案

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 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.