0

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

  • Show us the main queries used against this table. There are tricks that may be useful for speeding up the queries, hence decreasing the chance of deadlocks. – Rick James Nov 12 at 18:53
  • @Rick James updated – omri Nov 13 at 6:49
  • Sounds like PRIMARY KEY(name), INDEX(id) is slightly better. Perhaps the queries are part of longer transactions, and that is part of the deadlock hassle? – Rick James Nov 13 at 19:48
1

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

  • so from the post you related, you suggest using a staging table like in the example to pile up rows and then update the main table every x mins + truncating it? – omri Nov 13 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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