具有主键和唯一键的表的意外锁定

Question

对于同时具有主键和单独唯一索引的表上的事务，我遇到了innodb锁定问题。似乎如果TX使用唯一键删除一条记录，然后重新插入相同的记录，这将导致下一个键锁而不是预期的记录锁(因为键是唯一的)。请看下面的测试用例，以及我期望拥有什么锁的记录的细目：

DROP TABLE IF EXISTS foo; 
CREATE TABLE `foo` ( 
  `i` INT(11) NOT NULL, 
  `j` INT(11) DEFAULT NULL, 
  PRIMARY KEY (`i`), 
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; 
INSERT INTO foo VALUES (5,5), (8,8), (11,11); 

(注意:只需在单独的连接中，在TX1 TX2之后运行sql )

TX1

START TRANSACTION; 
DELETE FROM foo WHERE i=8; 

导致i=8上的排它锁(没有间隙锁，因为i是主键且是唯一的)

INSERT INTO foo VALUES(8,8); 

导致i=8和j= 8的独占锁定，以及i=6和i=7以及j=6和j=7的共享意向锁定

TX2

START TRANSACTION; 
INSERT INTO foo VALUES(7,7); 

导致i=7和j=7的独占锁定，以及i=6和j=6上的共享意向锁定

我希望TX2不会被TX1屏蔽，不管它是什么样子。奇怪的是，阻塞似乎与TX1的插入有关。我这样说是因为如果在delete之后没有运行TX1的insert语句，TX2的insert语句就不会被阻塞。几乎就像TX1重新插入(8,8)会导致索引j上的下一个键锁定(6,8]。

任何洞察力都将不胜感激。

Answer 1

您遇到的问题之所以会发生，是因为MySQL不只是为您要插入的值锁定表行，它还会按顺序锁定前一个id和下一个id之间的所有可能值，因此，请重用下面的示例：

DROP TABLE IF EXISTS foo;
CREATE TABLE `foo` (
  `i` INT(11) NOT NULL,
  `j` INT(11) DEFAULT NULL,
  PRIMARY KEY (`i`),
  UNIQUE KEY `jk` (`j`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
INSERT INTO foo VALUES (5,5), (8,8), (11,11);

假设您从transaction TX1开始：

START TRANSACTION;
REPLACE INTO foo VALUES(8,8);

然后，如果您启动一个事务TX2，则使用5到11之间的id的任何INSERT或REPLACE都将被锁定：

START TRANSACTION;
REPLACE INTO foo VALUES(11,11);

看起来MySQL使用这种锁定来避免这里描述的“幻影问题”：http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-locking.html，MySQL使用“下一个键锁定”，它结合了索引-行锁定和间隙锁定，这对我们来说意味着它将锁定前一个和下一个ids之间的许多可能的ids，也将锁定前一个和下一个ids。

为了避免这种情况，可以尝试创建一种服务器算法来插入您的记录，使插入到不同事务中的记录不会重叠，或者至少不会同时执行所有事务，这样TX就不必彼此等待。

Answer 2

https://bugs.mysql.com/bug.php?id=68021

这个bug问题回答了你的问题。

这是InnoDB的设计缺陷，上游用于修复此问题，以避免在读提交隔离中的row_ins_scan_sec_index_for_duplicate中的间隙锁定。然而，它带来了另一个问题，修复导致二级索引唯一键冲突静默，因此上游恢复此修复。