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 设置
