Minimizing and Handling Deadlocks Strategies I want to discuss deadlock minimizing strategies. From isolation level to retries to different insert strategies.

Lets say we have table A:

CREATE TABLE `A` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  `changing_data_a` VARCHAR(100) DEFAULT NULL,
  `changing_data_b` VARCHAR(100) DEFAULT NULL,
  UNIQUE KEY `name` (`name`),
  PRIMARY KEY (`id`),
) ENGINE=INNODB 


And lets say we get data from multiple providers(100k+ new/updated rows per day), name is unique and changing_data_a and changing_data_b data can be updated from time to time.  

Main queries to run against this table:


The instrets of course.
Selects by id
Selects by name
Updating changing_data columns by name


What would be your inserting strategy?


IODKU small batches from any number or threads(same as number of providers) with isolation level Read Committed and 3 retries in case of a deadlock.
Caching the data in memory of the application, inserting from a single thread every x minutes.
Caching the data in a help table without unique key, summarizing the data every x minutes


Any other strategy?

Also would like to add to this post the MySQL documentation regarding deadlocks handling: How to Minimize and Handle Deadlocks
This is a "normalization" table?  That is, it is a mapping from name to a shorter id?

If so, simply remove it from the transaction.  You don't really need to rollback if there is a failure.  At worst, you might have a few extra rows in the table that are never used.  Outside the main transaction, have autocommit=ON.  And, there is probably no need for IODKU, so simply use INSERT IGNORE.

Change from

UNIQUE KEY `name` (`name`),
PRIMARY KEY (`id`),


to

PRIMARY KEY(name),
INDEX(id)   -- sufficient for auto_increment


Rationale:  Multiple unique indexes slows down INSERTs.

Caveat:  Under what conditions do you look at the "changing_date" columns?  If you are coming from id, then your indexes are better due to clustering.

Another thing to note:  All(?) forms of INSERT, including IODKU, "burn" ids.  That is, you will find lots of gaps in the id values.

Since you are inserting at a mere 1+/second, I don't think you need any kind okf batching.  But if they do come to you in clumps, then here is a discussion of how to do that efficiently: http://mysql.rjweb.org/doc.php/staging_table#normalization