14.5.4 Phantom Rows 幻影行 所谓的幻读问题发生在一个事务 当相同的查询产生不同的结果集在不同的时间。 例如,如果一个SELECT 是执行2次,但是第2次返回的时间不第一次返回不同,行是变换的记录。 假设有一个索引是在child 表的id 列,需要读和锁定表的所有的记录 Id值大于100， 以便更新选择的记录的列 SELECT * FROM child WHERE id > 100 FOR UPDATE; 查询扫描 索引开始从第一个记录 id 是大于100.表包含记录id只有90和102. 如果锁设置在index records 在扫描的范围 不锁定插入到整个gaps（在这种情况下, gap是90到102） 另外的会话可以插入一个新值到表 id值为101. 如果你执行相同的SELECT 在相同的事务,你可能会看到一个新的激励 id值为101(一个幻读) 如果 我们注意 数据集为一个数据项,新的幻读child 会违反事务的隔离原则 以便在事务期间读取到的数据不会改变 CREATE TABLE `child` ( `sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号', `id` int(16) NOT NULL, `channelType` int(11) DEFAULT NULL COMMENT '通道识别', `status` tinyint(4) NOT NULL COMMENT '短信转态,1.发送成功,2.发送失败,3.发送异常', PRIMARY KEY (`sn`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表'; | 82 | 82 | 2 | 1 | | 83 | 83 | 2 | 1 | | 84 | 84 | 2 | 1 | | 85 | 85 | 2 | 1 | | 86 | 86 | 2 | 1 | | 87 | 87 | 2 | 1 | | 88 | 88 | 2 | 1 | | 89 | 89 | 2 | 1 | | 90 | 90 | 2 | 1 | | 102 | 102 | 2 | 1 | | 103 | 103 | 2 | 1 | | 104 | 104 | 2 | 1 mysql> create index child_idx1 on child(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Create Table: CREATE TABLE `child` ( `sn` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增编号', `id` int(16) NOT NULL, `channelType` int(11) DEFAULT NULL COMMENT '通道识别', `status` tinyint(4) NOT NULL COMMENT '短信转态,1.发送成功,2.发送失败,3.发送异常', PRIMARY KEY (`sn`), KEY `child_idx1` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8 COMMENT='短信发送成功记录表' 1 row in set (0.00 sec) Session 1： mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE; +-----+-----+-------------+--------+ | sn | id | channelType | status | +-----+-----+-------------+--------+ | 120 | 120 | 2 | 1 | | 121 | 121 | 2 | 1 | Session 2: 此时的区间为[90,无穷） 这个区间记录都无法插入 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(1,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(80,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(85,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(87,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(88,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(89,1,1); Query OK, 1 row affected (0.00 sec) mysql> insert into zjzc.child(id,channelType,status) values(90,1,1); --hang mysql> insert into zjzc.child(id,channelType,status) values(90,1,1); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> mysql> insert into zjzc.child(id,channelType,status) values(91,1,1); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into zjzc.child(id,channelType,status) values(101,1,1); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into zjzc.child(id,channelType,status) values(102,1,1);--hang mysql> insert into zjzc.child(id,channelType,status) values(103,1,1); ^CCtrl-C -- sending "KILL QUERY 2" to server ... Ctrl-C -- query aborted. ^[[AERROR 1317 (70100): Query execution was interrupted mysql> insert into zjzc.child(id,channelType,status) values(99999999,1,1); 全部锁住 为了防止幻读, InnoDB 使用一个算法叫做 next-key locking 组合了 index-row lock和gap lock. InnoDB 执行 row-level locking 以这样的方式 当他搜索或者扫描一个索引的时候, 它设置 共享或者排他锁 在遇到的index records上。因此, row-level locks are actually index-record locks. 此外,, a next-key lock 在一个Index record 也被称为"gap" 在那个index record 之前。 也就是说,一个next-key lock 是一个index-record lock加上一个区间锁 在index record之前的区间 如果一个session 有一个共享的或者排他的锁在记录R上在一个索引里, 另外的session 不能插入一个新的index 记录在这个区间 在记录R之前 当InnoDB 扫描一个索引,他也会锁定区间在最后的记录后面 就像前面的例子,为了阻止任何插入到表 id值是大于100 , 你可以使用 next-key locking来实现一个唯一性检查在你的应用里, 如果你读取你的数据在共享模式,不想看到重复对于一个你想要插入的记录, 然后呢可以安全的插入你的记录和知道 next-key lock 设置