http://xm-king.javaeye.com/blog/770721
他写的很好
这里再看看能够说明innodb repetable-read时避免幻读的例子：
client 1:
mysql> select version(); +------------------+ | version() | +------------------+ | 5.1.41-debug-log | +------------------+ 1 row in set (0.00 sec)
mysql> show variables like '%isola%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec)
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
client 2:
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
client 3:
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
注意 client3的操作都没有启动事务、i.e. 自动提交
---------------------------------------------------------------- 准备工作结束：
client 1:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
client 3:
4 rows in set (0.00 sec)
mysql> insert into x1 (s) values ('five'); Query OK, 1 row affected (0.05 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | +---+---------+ 5 rows in set (0.00 sec)
client 1:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
------------------------------------- innodb 在repetable-read级别不会出现幻读
client 2:
+---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | +---+---------+ 5 rows in set (0.00 sec)
------------------------------------- 由于client2从来没有select x1，此处虽然出现 five也不能说出现幻读
client 3:
mysql> insert into x1 (s) values ('size'); Query OK, 1 row affected (0.10 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
client 1:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | +---+---------+ 4 rows in set (0.00 sec)
client 2:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | +---+---------+ 5 rows in set (0.00 sec)
------------------------------------- innodb在repetable-read级别不会出现幻读
client 1:
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
client 2:
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
///
这里再看看能够说明innodb repetable-read时避免不可重复读的例子：
client 1:
mysql> show variables like '%isola%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tx_isolation | REPEATABLE-READ | +---------------+-----------------+ 1 row in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
client 2:
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
mysql> start transaction; Query OK, 0 rows affected (0.00 sec)
---------------------------------------------------------------- 准备工作结束：
client 1:
mysql> update x1 set s='four1' where i=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four1 | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
client 2:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
------------------------------------- innodb在repetable-read是不会出现脏读
client 1:
mysql> commit; Query OK, 0 rows affected (0.08 sec) mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four1 | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.00 sec)
client 2:
mysql> select * from x1; +---+---------+ | i | s | +---+---------+ | 1 | one | | 2 | two | | 3 | triple1 | | 4 | four | | 8 | five | | 9 | size | +---+---------+ 6 rows in set (0.01 sec)
------------------------------------- innodb在repetable-read是不会出现不可重复读
that's all, thank u
