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?

I've seen this error a couple times...it's InnoDB and row level locking at it's finest. Best solution is to uninstall MySQL and use Postgres ;) There is a lot of info on this error on the net, you aren't the only one with this issue. This link helped me in the past. http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/ — Twelfth, Oct 18 '13 at 21:19
Thank You! The link and the subsequent information I found on setting the transaction to 'READ COMMITTED' did the trick. I'll definitely consider moving to Postgres in the future, but given the focus and timeline of this project I'm going to stick with MySQL - given that I keep finding workarounds ;) — eh_whatever, Oct 22 '13 at 15:38
BTW this was my first post on here, so I'm not really sure how to give you credit for the answer since you only posted in the comment section — eh_whatever, Oct 22 '13 at 15:45
Just stumbled upon this. are you using windows? try restarting MySQL service in task manager. Tables tend to get locked when other programs/services are querying them then get stuck in loops (typically crashing) including workbench failing. — Bad_Neighbor, Jan 20 '19 at 00:49

score 6 · Answer 1 · answered Jun 02 '21 at 19:26

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

score 2 · Accepted Answer · edited Oct 24 '13 at 21:13

2

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.

edited Oct 24 '13 at 21:13

p.campbell

98,673
67
256
322

answered Oct 22 '13 at 16:51

Twelfth

7,070
3
26
34

4

Odd how many consistent downvotes this answer get. Must be a few MySQL loyalist out there. – Twelfth May 31 '17 at 15:33
1

I'll give the benefit of the doubt that this answer is from 2013, and that advice today wouldn't be to "get off MySql". Otherwise we can talk about how Github, Uber, and other large companies make MySql scalable for their purposes. – contactmatt Jul 10 '18 at 15:42
@contactmatt - yes, old answer that gets alot of downvotes as time goes on. I believe uber is using the enterprise version, if you are willing to pay mysql is a decent choice. This post was written prior to 5.8. People stuck in old versions still deal with theae issues. – Twelfth Jul 30 '18 at 03:39

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

2 Answers2

Linked