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

Source: Internet
Author: User
Tags one more line mysql command line

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====



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.