Mysql – Minimizing and Handling Deadlocks Strategies

deadlockinnodbMySQL

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:

  1. The instrets of course.
  2. Selects by id
  3. Selects by name
  4. Updating changing_data columns by name

What would be your inserting strategy?

  1. 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.
  2. Caching the data in memory of the application, inserting from a single thread every x minutes.
  3. 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

Best Answer

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

Related Question