0

I am updating a large table with millions on rows on the primary KEY and it causes transaction lock (Lock wait time exceeded).

The query is an update on the primary key, here is the exact query execute:

UPDATE product p set p.id_image = 12646124 WHERE p.id_product =7835453

Here is the explain statement: https://snag.gy/eEjK5R.jpg

I need to mention i have many indexes in this table, composed indexed most of them, i am using them for filtering and so on, i do not know if this can be the issue, the strange part is that this only happens sometimes, when the ID's are different in the save query, it does not timeout.

Pepelea Razvan Ionut
  • 105
  • 9
  • 2
    Why do you want order by in update query, if there is no join please use alias . – Rahul Singh Apr 22 '19 at 09:49
  • A transaction lock happens when a query overlaps with *another* transaction. This means a) that posting just one query without information about what other kind of queries are running does not help and b) that it might be a temporary situation and/or only happen coincidental (you call it "sometimes"). We cannot investigate this at all with a single query, but you can try to find out what is running/blocking at the same time using e.g. [this](https://stackoverflow.com/q/2766785). – Solarflare Apr 22 '19 at 11:24
  • @RahulSingh, i do not want o update and order, i added that as "fix" for the problem, it is a solution i found online, but it did not work. i have removed it. – Pepelea Razvan Ionut Apr 22 '19 at 14:27
  • @Solarflare - Thank you, i will check – Pepelea Razvan Ionut Apr 22 '19 at 14:28
  • Please provide `SHOW CREATE TABLE` and what other queries were running at the same time. – Rick James May 15 '19 at 05:59

0 Answers0