1

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.
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".

  • What other answer? – Greg Nov 15 '16 at 3:38

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.