MySQL Error Code: 1205. Lock wait timeout during update with inner join

Question

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?

Answer 1

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.

Answer 2

Try see if your MySQL Server had a process in running.

To see that, run the command on MySQL Client:

SHOW FULL PROCESSLIST;

See id, state and info columns and analyze the problem. After you find the problem, kill the process with:

KILL <PROCESS ID>;