MySQL Phantom read

Phantom Read (Phantom read) is when a user reads a range of data rows, the B transaction inserts a new row within that range, and when the user reads the data row of that range, a new phantom row is found. The InnoDB and Falcon storage engines solve the Phantom reading problem through multiple versioning concurrency control mechanisms.

http://bbs.csdn.net/topics/360050896

http://narcissusoyf.iteye.com/blog/1637309

http://hudeyong926.iteye.com/blog/1490687

Http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode

Http://www.cnblogs.com/zhaoyl/p/4121010.html

A transaction reads the new data that has been submitted by the B transaction, at which point A has not yet been committed, and after the current commit, you will see

A transaction

Mysql> select * from test;
+----+----+
| A | B |
+----+----+
| 14 | 14 |
| 16 | 16 |
| 10 | 20 |
| 31 | 31 |
| 33 | 33 |
| 38 | 38 |
| 50 | 60 |
| 70 | 70 |
+----+----+
8 rows in Set (0.00 sec)

Transaction b

mysql> INSERT INTO Test (A, B) values (80,80);
Query OK, 1 row Affected (0.00 sec)

Transaction A

Mysql> select * from test;
+----+----+
| A | B |
+----+----+
| 14 | 14 |
| 16 | 16 |
| 10 | 20 |
| 31 | 31 |
| 33 | 33 |
| 38 | 38 |
| 50 | 60 |
| 70 | 70 |
+----+----+
8 rows in Set (0.00 sec)

Transaction b

Mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Transaction a does not see the data for the insert of transaction B if it is not a commit at this time

Mysql> select * from test;
+----+----+
| A | B |
+----+----+
| 14 | 14 |
| 16 | 16 |
| 10 | 20 |
| 31 | 31 |
| 33 | 33 |
| 38 | 38 |
| 50 | 60 |
| 70 | 70 |
+----+----+
8 rows in Set (0.00 sec)

Commit

Mysql> select * from test;
+----+----+
| A | B |
+----+----+
| 14 | 14 |
| 16 | 16 |
| 10 | 20 |
| 31 | 31 |
| 33 | 33 |
| 38 | 38 |
| 50 | 60 |
| 70 | 70 |
| 80 | 80 |
+----+----+
9 Rows in Set (0.00 sec)

MySQL Phantom read