Innodb updates records with delay

Question

I have two tables in my database like these:

CREATE TABLE `main` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `sub` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `main_id` int(11) NOT NULL DEFAULT '0',
  `count` int(11) NOT NULL DEFAULT '0',
  `done` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY (`main_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have queries like these:

INSERT INTO `main` (`id`, `type`, `status`) VALUES (NULL, '1', '1');
INSERT INTO `sub` (`id`, `main_id`, `count`, `done`) VALUES (NULL, '1', '2', '0');
update sub set done = done + 2  where main_id =1

After these three , my app select the record using a query like this:

SELECT * FROM `sub`  where main_id =1

My problem is that sometimes (not always) the select query returns the data with delay. For example it returns the data after 10 minutes while thousands of other records are updated with no delay.

P.S: There is no caching layer on the app. P.S: There is no caching in MySql.

score 1 · Answer 1 · edited Jun 15 '20 at 09:05

There are two things you must remember is at play

TRANSACTION ISOLATION

The default for tx_isolation is REPEATABLE-READ.

It is plausible and probable that the update sub set done = done + 2 where main_id =1 is being held up.

In fact, your INSERTs must generate auto_increment values. This requires doing some sort of gap index locking. If other INSERTs are occurring on main and sub,

LOCKS ON DATA

You could potentially catch such delays in the act by running

SHOW ENGINE INNODB STATUS\G

The update sub set done = done + 2 where main_id =1 might be visible at some point due to any row-level locks and/or index-level locks.

Locking of the PRIMARY KEY for auto_increment generation must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

I actually discussed this in a later post : How to analyse innodb status on deadlock in insert Query?

Innodb updates records with delay

1 Answers1

TRANSACTION ISOLATION

LOCKS ON DATA