How does lock works when delete rows through secondary index in InnoDB? 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 (&quot;Post&quot;, 6);
insert into follows(followable_type, followable_id) values (&quot;Post&quot;, 22);
insert into follows(followable_type, followable_id) values (&quot;Post&quot;, 28);
insert into follows(followable_type, followable_id) values (&quot;Post&quot;, 32);
insert into follows(followable_type, followable_id) values (&quot;Post&quot;, 34);

step4: begin two transactions
Session1
session1&gt; begin;
session1&gt; delete from follows where followable_id=28;

Session2
session2&gt; begin;
session2&gt; insert into follows(followable_type, followable_id) value (&quot;Post&quot;, 22);  
Lock wait timeout exceeded; try restarting transaction

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

session2&gt;insert into follows(followable_type, followable_id) value (&quot;Post&quot;, 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:

How to explain the conflict?
Could you explain the meaning of DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters.
(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".