【MySQL】快照读、当前读

需了解知识点：MySQL的存储引擎InnoDB的事务隔离级别REPEATABLE READ。

 

快照读：读取的是快照版本，也就是历史版本。普通的SELECT就是快照读，

当前读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT …  LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

 

mysql> use test;

Database changed

	session1	session2
T1	set autocommit = 0;	set autocommit = 0;
T2	select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)	select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set, 1 warning (0.00 sec)
T3	select * from product; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | +----+-----+ 12 rows in set (0.00 sec)	select * from product; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | +----+-----+ 12 rows in set (0.00 sec)
T4		mysql> insert into tb1 values(13,13); Query OK, 1 row affected (0.00 sec)
T5		mysql> commit; Query OK, 0 rows affected (0.00 sec)
		mysql> select * from tb1; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | | 13 |  13 | +----+-----+ 13 rows in set (0.00 sec)
T6	mysql> select * from tb1; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | +----+-----+ 12 rows in set (0.00 sec)
T7	mysql> select * from tb1 lock in share mode; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | | 13 |  13 | +----+-----+ 13 rows in set (0.00 sec)
T8	mysql> commit; Query OK, 0 rows affected (0.00 sec)   mysql> select * from tb1; +----+-----+ | id | id2 | +----+-----+ |  1 |   1 | |  2 |   2 | |  3 |   3 | |  4 |   4 | |  5 |   5 | |  6 |   6 | |  7 |   7 | |  8 |   8 | |  9 |   9 | | 10 |  10 | | 11 |  11 | | 12 |  12 | | 13 |  13 | +----+-----+ 13 rows in set (0.00 sec)