I am trying to avoid a future problem.

Table has 2 fields:

id -> bigint Primary key

hash -> varchar(65) unique key

there will be concurrent inserts into the table for hash. I need the last insert id

So question: INSERT INTO table (hash) VALUES(blah) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) Generate deadlocks if there are 30-50 threads, there is a high chance that these threads will try to insert the same blah?

OR do I need to do: SELECT id FROM table WHERE hash=blah if exists GREAT

if doesnt exist:

INSERT IGNORE INTO table (hash) VALUES(blah);

SELECT id FROM table WHERE hash=blah

Edit: Here is the table:

CREATE TABLE `hash_list` (
  `hash` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_UNIQUE` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

I have no control over the hash. All I need is to store the hash ONCE and get the id many times so it can be used elsewhere in the application. Without deadlocks. This way I get storage savings that will become significant over time.

Edit 2: I've marked danblack's answer as accepted because of the ROW_COUNT() suggestion. But Rick James has valid points as well. Both answers are great!

Edit 3: interestingly with 100+ plus I am seeing 0 deadlocks.

  • id looks by the update part of your insert to be the secondary field and hash is the pk? Is varchar(65) the smallest the hash can be? consider binary(X) and remove any hex, base64 etc encoding on it as you insert it. With hash as the pk do you need id? Do you really want to update the id on duplicate? or ignore?
    – danblack
    Sep 16, 2021 at 22:27
  • Please post TEXT results of SHOW CREATE TABLE tbl_name; so we can see what the system is working with. Sep 16, 2021 at 23:29
  • 1
    @WilsonHauck I added the table definition. Thanks! Sep 17, 2021 at 3:30
  • @danblack this table is not set in stone, its my interpretation of what I need. I need to store the hash, that I have no control over, once and get the id of the row where it is stored, so the application can use that id elsewhere. Sep 17, 2021 at 3:31

2 Answers 2


So to summarize the problem, input is a hash that needs to be stored. And the auto_increment of that ID is needed.

You've done the right thing and made hash a unique key.

INSERT IGNORE INTO hash_list (hash) VALUES(?);

Is correct. In the application API or SQL (ROW_COUNT()) you will get a rows affected. 0 indicates that it already existed.

If it already existed you'll need to fetch the id with:

SELECT id FROM hash_list WHERE hash=?

If the ROW_COUNT() is 1, then the LAST_INSERT_ID() (both of which are in the application API so a separate query isn't needed) is the id you need.

Recommend using a binary type for the hash, as by its name it isn't going to be utf8mb4.

Depending if you are needing to insert more or retrieve the hash by id more, you can make hash the primary key and id a unique AUTO_INCREMENT key as follows:

CREATE TABLE `hash_list` (
  `hash` varbinary(45),
  PRIMARY KEY (`hash`),
  UNIQUE KEY `id` (`id`)

The above queries will work regardless of the table structure provided hash is a unique key or a primary key. The AUTO_INCREMENT column needs to be indexed.

ref: fiddle

  • Well damn!! I didn't even think of the ROW_COUNT(), Thanks for that!! may i ask: why varbinary? is varbinary faster over varchar? Sep 17, 2021 at 3:59
  • 1
    varbinary / binary has no character set. So there's storage overhead or implicit conversions associated with it. Depending on the hash, it can have a fixed width. You can remove the encoding (hex/base64) of it too and store it in its minimal binary. You can also use the virtual generated column in the table to expose a text form if you want.
    – danblack
    Sep 17, 2021 at 4:02

What client language are you using? There may be a way to get the id without touching the table again. It is called LAST_INSERT_ID() in some APIs.

INSERT IGNORE INTO table (hash) VALUES(blah);
reach for last_insert_id in the API.  Or possibly

Be aware that IGNORE and IODKU will "burn" an id if it does not actually insert. (Since you are using BIGINT, you won't run out of ids.)

Also, consciously decide what "transaction" to include the INSERT in. Note that your 2-step (SELECT; if then INSERT) must be in a transaction and the SELECT must have FOR UPDATE. Hence, for a single insert, IODKU is better.

If this is a "normalization" table, and you expect to bulk-load it, see this for more tips: http://mysql.rjweb.org/doc.php/staging_table#normalization The advice there avoids burning ids, is fast, etc.

If that hash is hex, you may as well declare it CHARACTER SET ascii COLLATE ascii_general_ci.

  • I am using PHP. I indeed have LAST_INSERT_ID and ROW_COUNT(). The burn really doesnt matter, bigint should take care of it. Sep 17, 2021 at 4:08
  • Thank you for the answer! I will test both your and @danblack answer and see which is faster. For now its time for bed:) Sep 17, 2021 at 4:15
  • @AndriyLysak - Be aware that you cannot avoid all deadlocks. So, check for errors and be ready to replay the transaction if it deadlocks. How many CPU cores do you have? There is a higher risk of deadlocks and poor latency if the number of threads exceeds cores.
    – Rick James
    Sep 17, 2021 at 4:26
  • 2 cores... 50+ threads, will put it in a try catch and see what happens:) Thanks Sep 17, 2021 at 13:37
  • 1
    @AndriyLysak - Instead of that, develop a way to gather, say, 100 or on minute's worth of items before putting them into the table. Batching cay run 10 times as fast.
    – Rick James
    Sep 17, 2021 at 21:25

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.