0

I am working on an apparent performance problem possibly involving row lock contention, and I'm seeking to understand whether INSERT ... ON DUPLICATE KEY UPDATE Foo=Foo (with a no-op in the UPDATE clause) obtains the same kind of lock as it would if the UPDATE clause were not a no-op, or if MySQL can recognize the no-op and obtain a less-expensive lock. Googling for the relevant documentation has thus far failed to provide an answer either way.

Improve this question
4
  • If you need in no-op UPDATE clause then use not INSERT .. ON DUPLICATE KEY UPDATE but INSERT IGNORE.
    – Akina
    May 11 at 5:22
  • Need some context -- What else is going on? Let's see the query and SHOW CREATE TABLE. How many rows are being inserted at once?
    – Rick James
    May 11 at 16:16
  • And,... If your version handles such, provide EXPLAIN INSERT ...
    – Rick James
    May 11 at 16:16
  • This is a single row insert that should hit the table's unique key. There is no WHERE. ``` +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------------+------+---------+------+------+----------+-------+ | 1 | INSERT | my_table | NULL | ALL | my_unique_key | NULL | NULL | NULL | NULL | NULL | NULL ```
    – cbmanica
    May 11 at 16:33

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.