InnoDB默认的隔离级别是RR(可重复读),可以解决脏读和不可重复读,只解决了快照读情况下的幻读问题,当前读情况下解决幻读问题得靠next-key锁。
mysql如何实现避免幻读:
在快照读读情况下,mysql通过mvcc来避免幻读。
在当前读读情况下,mysql通过next-key来避免幻读
- 快照读, 读取专门的快照 (对于RC,快照(ReadView)会在每个语句中创建。对于RR,快照是在事务启动时创建的)
```
简单的select操作即可(不需要加锁,如: select ... lock in share mode, select ... for update)
```
针对的也是select操作
- 当前读, 读取最新版本的记录, 没有快照。 在InnoDB中,当前读取根本不会创建任何快照。
```
select ... lock in share mode
select ... for update
insert
update
delete
针对如下操作, 会让如下操作阻塞:
```
insert
update
delete
```
- 在RR(可重复读)级别下, 快照读是通过MVVC(多版本控制)和undo log来实现的,
当前读是通过手动加record lock(记录锁)和gap lock(间隙锁)来实现的。所以从上面的显示来看,如果需要实时显示数据,还是需要通过加锁来实现。这个时候会使用next-key技术来实现。所以说
事务的隔离级别:
快照读的情况下解决幻读问题:
脏读:事务A读取了事务B提交的数据,但是B事务由于某种原因导致事务回滚,但是A读取的仍然是事务B回滚之前的数据
不可重复读:事务A读取了一条数据,这时事务B将该条数据修改,事务A再次读取该条数据时,和最开始读取的数据不一致
幻读:事务A读取了一批数据,例如select * from user where age =10;读取出了5调数据。这时事务B又向user表插入了一条数据
insert into user(age)values(10)。事务A再次查询时,会发现多了一条数据,这就是幻读。
什么是MVCC?
多版本并发控制。InnoDB为每行记录添加了一个版本号(系统版本号),每当修改数据时,版本号加一。
在读取事务开始时,系统会给事务一个当前版本号,事务会读取版本号<=当前版本号的数据,这时就算另一个事务插入一个数据,并立马提交,新插入这条数据的版本号会比读取事务的版本号高,因此读取事务读的数据还是不会变。
例如:
此时books表中有5条数据,版本号为1
事务A,系统版本号2:select * from books;因为1<=2所以此时会读取5条数据。
事务B,系统版本号3:insert into books ...,插入一条数据,新插入的数据版本号为3,而其他的数据的版本号仍然是2,插入完成之后commit,事务结束。
事务A,系统版本号2:再次select * from books;只能读取<=2的数据,事务B新插入的那条数据版本号为3,因此读不出来,解决了幻读的问题。
当前读 (current read)解决幻读问题:
1.打开客户端1查看隔离级别及初始数据
mysql> SELECT @@SESSION.tx_isolation;
+------------------------+
| @@SESSION.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
+----+-----------+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql>
2.打开客户端2查看隔离级别及初始数据
mysql> SELECT @@SESSION.tx_isolation;
+------------------------+
| @@SESSION.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+
1 row in set (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
+----+-----------+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql>
3.在客户端2中开启事务, 然后查询数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
+----+-----------+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql>
4.在客户端1中插入一条id为4的新数据 (直接自动提交)
mysql> insert into test_transaction (`id`,`user_name`,`age`,`gender`,`desctiption`) values (4, '死侍', 18, 0, 'A bad boy');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
| 4 | 死侍 | 18 | 0 | A bad boy |
+----+-----------+-----+--------+--------------------+
4 rows in set (0.00 sec)
mysql>
5.在客户端2事务中再次查询数据, 发现数据没有变化(表示可以重复读, 并且克服了幻读)!! 但是在客户端2事务中插入一条id为4的新数据, 发现提示数据已经存在!!!
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
+----+-----------+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql> select * from test_transaction;
+----+-----------+-----+--------+--------------------+
| id | user_name | age | gender | desctiption |
+----+-----------+-----+--------+--------------------+
| 1 | 金刚狼 | 127 | 1 | 我有一双铁爪 |
| 2 | 钢铁侠 | 120 | 1 | 我有一身铁甲 |
| 3 | 绿巨人 | 0 | 2 | 我有一身肉 |
+----+-----------+-----+--------+--------------------+
3 rows in set (0.00 sec)
mysql> insert into test_transaction (`id`,`user_name`,`age`,`gender`,`desctiption`) values (4, '死侍', 18, 0, 'A bad boy');
1062 - Duplicate entry '4' for key 'PRIMARY'
mysql>
//并且, 此时`update/delete`也是可以操作这条在事务中看不到的记录的!
6.那么这是什么问题呢?
可以参考MySQL官方文档 -- 一致性非阻塞读
The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.
个人认为应该翻译为: 数据库状态的快照适用于事务中的SELECT语句, 而不一定适用于所有DML语句。 如果您插入或修改某些行, 然后提交该事务, 则从另一个并发REPEATABLE READ事务发出的DELETE或UPDATE语句就可能会影响那些刚刚提交的行, 即使该事务无法查询它们。 如果事务更新或删除由不同事务提交的行, 则这些更改对当前事务变得可见。
当然, 使用隔离性的最高隔离级别SERIALIZABLE也可以解决幻读, 但该隔离级别在实际中很少使用!