How does lock works when delete rows through secondary index in InnoDB?

Question

step1: InnoDB Transaction Isolation Level

Repeated Read.

step2: Table schema

CREATE TABLE `follows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `followable_type` varchar(255) DEFAULT NULL,
  `followable_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type_first` (`followable_type`,`followable_id`),
  KEY `id_first` (`followable_id`,`followable_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

step3: prepare test data

insert into follows(followable_type, followable_id) values ("Post", 6);
insert into follows(followable_type, followable_id) values ("Post", 22);
insert into follows(followable_type, followable_id) values ("Post", 28);
insert into follows(followable_type, followable_id) values ("Post", 32);
insert into follows(followable_type, followable_id) values ("Post", 34);

step4: begin two transactions

Session1

session1> begin;
session1> delete from follows where followable_id=28;

Session2

session2> begin;
session2> insert into follows(followable_type, followable_id) value ("Post", 22);  
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 28);  
Lock wait timeout exceeded; try restarting transaction

session2>insert into follows(followable_type, followable_id) value ("Post", 32);  
Query OK, 1 row affected (0.01 sec)

Test Result

MySQL manual claim

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

https://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html

Actually, session1 locked this range [22, 32), which consists of

• record locks (those index records equal 22)
• a next key lock(22, 28]
• a gap lock (28, 32)

Meaning of() (]

• (): Gap lock is a lock on a gap between index records, I use bracket () to stand for.

• (] Next key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. I use (] stand for it.

If manual is right, the result should be (22, 28]. So I really confused by conflict between test result and MySQL manual:

1. How to explain the conflict?
2. Could you explain the meaning of DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.

Answer 1

(Semi-related comments...)

Assuming this is a standard many-to-many mapping table, get rid of id entirely. Then make one of the other KEYs (either one) the PRIMARY KEY. More tips here

If the DELETE is typical, then I would make the KEY starting with followable_id the PRIMARY KEY.

This will speed up the inserts and the deletes, thereby tending to avoid deadlocks and "lock waits".