ON DUPLICATE KEY causing timeout

Question

I am currently running the following SQL statement on my MySQL database:

INSERT INTO `Table` (`Col1`, `Col2`, `Col3`) VALUES ('a','b','c')
ON DUPLICATE KEY UPDATE `Col1`=`Col1`, `Col2`=`Col2`, `Col3`=`Col3`

However, everytime I do run it, I get the following error: #1205 - Lock wait timeout exceeded; try restarting transaction. It's pretty horrible, because I don't know what exactly went wrong. My intention is to update all the values of the existing row (with the exception of id, which is the table's primary key).

If you do have any idea, please let me know!

EDIT: More info... the id is an auto-incremented field. There is also another UNIQUE column (let's call it IdentityNo, and so what I'd like the statement to do is to: 1. insert if there is no conflict in IdentityNo, and 2. update that existing row if there is a conflict (without changing the id).

What are you expecting `ON DUPLICATE KEY` to do when you don't use `id` in the INSERT? Is any of the other values indexed as `UNIQUE`? — Joachim Isaksson, Aug 29 '12 at 19:40
Sorry I missed out my intention, have updated the information...! — matt, Aug 29 '12 at 19:45
You're updating the table with the same values? That seems pointless. Maybe you mean `VALUES(Col1)`. — tadman, Aug 29 '12 at 19:52

score 0 · Accepted Answer · answered Aug 29 '12 at 20:28

If you want to refer to the values of the columns in the insert section of the above statement, you need to use values(column_name). You must use something like INSERT INTO Table (Col1, Col2, Col3) VALUES ('a','b','c') ON DUPLICATE KEY UPDATE Col1=values(Col1), Col2=values(Col2), Col3=values(Col3)

ON DUPLICATE KEY causing timeout

1 Answers1