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_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
changing_datacolumns by name
What would be your inserting strategy?
IODKUsmall batches from any number or threads(same as number of providers) with isolation level
Read Committedand 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