mysql-RR级别存在幻读问题

mysql-RR级别存在幻读问题

结论：RR级别下存在幻读，但也可以避免

快照读和当前读

• 快照读
  • 读取版本链上最新可见的数据
• 当前读
  • select * from table for update
  • select * from table in share mode

准备

mysql> select * from t_user;
+----+-------------------+--------+
| id | user_name         | age    |
+----+-------------------+--------+
|  1 | test2dsd          |    121 |
|  2 | sdfsd23323        | 123234 |
|  3 | sdseerer          |     32 |
|  4 | sdfsdfdsfsd       |     33 |
|  5 | tomw232232-2      |    323 |
|  6 | sadfsadsdf-cdsfsd |     12 |

RR幻读模拟

步骤：

• 事务A先快照读，然后进行当前读
• 事务B在事务A进行快照读后，再插入数据，最后提交(一定要提交)
• 此时事务A的当前读就可以读取到事务B新增的数据

事务A:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# 事务B插入数据之前进行快照读
mysql> select * from t_user where id >3;
+----+-------------------+-----+
| id | user_name         | age |
+----+-------------------+-----+
|  4 | sdfsdfdsfsd       |  33 |
|  5 | tomw232232-2      | 323 |
|  6 | sadfsadsdf-cdsfsd |  12 |
+----+-------------------+-----+
3 rows in set (0.02 sec)

# 事务B插入数据之后进行快照读
mysql> select * from t_user where id >3;
+----+-------------------+-----+
| id | user_name         | age |
+----+-------------------+-----+
|  4 | sdfsdfdsfsd       |  33 |
|  5 | tomw232232-2      | 323 |
|  6 | sadfsadsdf-cdsfsd |  12 |
+----+-------------------+-----+
3 rows in set (0.02 sec)

# 事务B插入数据之后进行当前读
mysql> select * from t_user where id >3 for update;
+----+-------------------+-----+
| id | user_name         | age |
+----+-------------------+-----+
|  4 | sdfsdfdsfsd       |  33 |
|  5 | tomw232232-2      | 323 |
|  6 | sadfsadsdf-cdsfsd |  12 |
|  8 | tom               | 323 |
+----+-------------------+-----+

事务B:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `t_user` (`user_name`, `age`) VALUES ('tom', 323);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

按照正常的RR隔离级别，在事务B插入数据提交事务后，数据对正在进行的事务A是不可见，但是在事务A使用当前读的时候却读取到了事务B新增的数据

RR下避免幻读

事务A:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 在事务B插入数据之前进行当前读
mysql> select * from t_user where id >3 for update;
+----+-------------------+-----+
| id | user_name         | age |
+----+-------------------+-----+
|  4 | sdfsdfdsfsd       |  33 |
|  5 | tomw232232-2      | 323 |
|  6 | sadfsadsdf-cdsfsd |  12 |
|  8 | tom               | 323 |
+----+-------------------+-----+
4 rows in set (0.02 sec)

事务B:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `flink-db`.`t_user` (`user_name`, `age`) VALUES ('tom2', 323);
1205 - Lock wait timeout exceeded; try restarting transaction

可以发现，当事务A进行当前读的时候，事务B插入新增数据的操作是被阻塞的，需要等事务A将事务提交

good luck!