The MySQL transaction isolation level is the "phantom read" phenomenon under "Repeatable-read"

The MySQL transaction isolation level is the "phantom read" phenomenon under "Repeatable-read"

Statements about transaction control in the MySQL command line see this article:http://my.oschina.net/xinxingegeya/blog/296459

This film reference article: http://blog.csdn.net/jiao_fuyou/article/details/16368827

Http://www.cnblogs.com/hancf/archive/2012/08/28/2660422.html

Repeatable READ in MySQL

This opens two MySQL command-line windows, window A, session1, window B, or Session2.

Session1

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM t1;+----+------+| A | b |+----+------+| 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | |+----+------+4 rows in Set (0.00 sec)

Session2

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> insert into T1 values (55, 3000); Query OK, 1 row Affected (0.00 sec) Mysql> commit; Query OK, 0 rows affected (0.03 sec) mysql> select * FROM t1;+----+------+| A | b |+----+------+| 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | |+----+------+5 rows in Set (0.00 sec)

Session2 transaction has been shown to commit

Session1

Now return to Session1 for the following actions

Mysql> SELECT * FROM t1;+----+------+| A | b |+----+------+| 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | |+----+------+4 rows in Set (0.00 sec)

At this point, although a piece of data is inserted in Session2 and the transaction is committed, the query in Session1 and the Session1 's last query are the same result, which is repeated reading . If it is at the "read-committed" level, you can read the a=55 record.

Test MySQL Repeatable read (ii)

Session1

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from t1 where a = 56;+----+------+| A | b |+----+------+| 56 | 7000 |+----+------+1 row in Set (0.00 sec)

Session2

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> Update T1 set B = 8000 where a = 56; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0mysql> select * from t1 where a = 56;+----+- -----+| A | b |+----+------+| 56 | 8000 |+----+------+1 row in Set (0.00 sec) Mysql> commit; Query OK, 0 rows affected (0.04 sec)

Session1

Mysql> SELECT * from t1 where a = 56;+----+------+| A | b |+----+------+| 56 | 7000 |+----+------+1 row in Set (0.00 sec)

Consistent with last query results, verified repeat read ...

At this point, if Session1 commits the transaction and re-opens the transaction, the query can find the result modified in Session2

Mysql> commit; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from t1 where a = 56;+----+------+| A | b |+----+------+| 56 | 8000 |+----+------+1 row in Set (0.00 sec)

The Phantom reading under the Repeatable-read

Session1

Mysql> begin; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * FROM t1;+----+------+| A | b |+----+------+| 51 | 3000 | | 52 | 3000 | | 53 | 3000 | | 54 | 3000 | | 55 | 4000 | | 56 | 8000 |+----+------+6 rows in Set (0.00 sec)

Session2

mysql> begin; query ok, 0 rows affected  (0.00 sec) mysql> select * from  t1;+----+------+| a  | b    |+----+------+| 51 | 3000  | |  52 | 3000 | |  53 | 3000 | |  54 | 3000 | |  55 | 4000 | |  56 | 8000 |+----+------+6 rows in set  (0.00 sec) mysql>  insert into t1 values  (57, 1000); query ok, 1 row affected  (0.00 sec) mysql> select * from  t1;+----+------+| a  | b    |+----+------+| 51 | 3000  | |  52 | 3000 | |  53 | 3000 | |  54 | 3000 | |  55 | 4000 | |  56 | 8000 | |  57 | 1000 |+----+------+7 rows in set  (0.00 sec) mysql> commit; query ok, 0 rows affected  (0.11 SEC)

Session1

mysql> Update T1 Set B = b+1000; Query OK, 7 rows Affected (0.00 sec) Rows Matched:7 changed:7 warnings:0mysql> select * FROM t1;+----+------+| A | b |+----+------+| 51 | 4000 | | 52 | 4000 | | 53 | 4000 | | 54 | 4000 | | 55 | 5000 | | 56 | 9000 | | 57 | |+----+------+7 rows in Set (0.00 sec)

Session1 SQL for the entire session

mysql> begin; query ok, 0 rows affected  (0.00 sec) mysql> select * from  t1;+----+------+| a  | b    |+----+------+| 51 | 3000  | |  52 | 3000 | |  53 | 3000 | |  54 | 3000 | |  55 | 4000 | |  56 | 8000 |+----+------+6 rows in set  (0.00 sec) mysql>  select * from t1;+----+------+| a  | b    |+----+------ +| 51 | 3000 | |  52 | 3000 | |  53 | 3000 | |  54 | 3000 | |  55 | 4000 | |  56 | 8000 |+----+------+6 rows in set  (0.00 sec) mysql>  update t1 set b = b+1000; query ok, 7 rows affected  (0.00 sec) rows Matched: 7  changed: 7  warnings: 0mysql> select * from  t1;+----+------+| a  | b    |+----+------+| 51 |  4000 | |  52 | 4000 | |  53 | 4000 | |  54 | 4000 | |  55 | 5000 | |  56 | 9000 | |  57 | 2000 |+----+------+7 rows in set  (0.00 sec) mysql>

Can see one more line, this is the Phantom read ...

But there is a problem, still do not understand, magic reading and non-repeatable reading what is the difference? Online Search a lot of information or say that do not understand, hope to have a good understanding of students can exchange AH ...

=====end====