Avoid Deadlock on concurrent insert

Question

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` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `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.

Answer 1

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` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `hash` varbinary(45),
  PRIMARY KEY (`hash`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB

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

Answer 2

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
SELECT LAST_INSERT_ID();

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.