mysql 不让读的锁_[译]MySQL不加锁实现一致性读的机制分析

MySQL的一致性读的机制是是这样实现的：InnoDB引擎为一个事务Tx提供一个在时间T1的版本快照(T1就是在本

事务中首次执行查询语句的时间点)。事务Tx中可以查询到时间点T1之前提交的数据，时间点T1之后提交的数据在

Tx中是看不到的。唯一的例外Ex是在事务Tx中可以看到在本事务中提交的数据(即便是在T1时间点还没有提交的数据)。

先建一个表，边理论边实践，具体看下MySQL是如何工作的。

mysql> create tablemvcc(-> id int primary key,-> name varchar(20),-> city varchar(20)-> ) engine innodb defaultcharset utf8->;

Query OK,0 rows affected (0.01 sec)

新建数据库连接，假定为session1，在session1中做如下操作：

mysql> set session transaction isolation level repeatable read;

Query OK,0 rows affected (0.00sec)

mysql> set autocommit=off;

Query OK,0 rows affected (0.00sec)

mysql> insert into mvcc(id,name,city) values(1,‘name1‘,‘city1‘);

Query OK,1 row affected (0.00sec)

mysql> select * frommvcc; T1+----+-------+-------+

| id | name | city |

+----+-------+-------+

| 1 | name1 | city1 |

+----+-------+-------+

1 row in set (0.00 sec)

在上面的实验中可以看到，本事务Tx中T1时间前插入的还未提交的数据，在T1时间新建的快照里面是可以看到的。

这里说是快照，其实不是严格意义上的快照(其实是在T1点做个标记，T1之后其他事务提交的数据，利用undo log回滚，得出旧数据)

这个时候，其实事务Tx，还有其他事务，都可以更新T1时间点的快照数据。

这个例外Ex会导致如下的异常：如果你在Tx中更新了表的数据，在Tx中的Select语句会看到数据这些更新的数据，但是此时Select还可能会看到

某些行的比较老的数据(T1后有其他事务进行了数据的更新，但是在事务Tx中是看不到这些更新的，即便是其他事务提交了，所有上面这些都是在Repeatable read隔离级别的情况)

这个异常异味着在事务Tx的select看到的状态不是数据库真是的状态。

如果数据库的隔离级别是Repeatable Read隔离级别的(MySQL的默认级别)，在事务Tx中所有的select语句所看到的快照都是跟Tx中第一条select

看到的数据是一样的。如果你先给在tx中得到新的快照，只能先commit本事务，在新开事务

如果数据库的隔离级别是READ COMMITTED级别的话，在Tx中的每次select操作都会重新得到一份最新的快照。

一致性读在Innodb引擎的在READ COMMITTED和REPEATABLE READ隔离级别下执行SELECT语句的时候的默认行为。一致性读不会在所读的表上添加

锁，所以其他session也可以同时修改这个表的数据。

如果你的MySQL数据库运行在默认的REPEATABLE READ的隔离级别的话。当你发出一个一致性读(也就是普通的SELECT语句)。Innodb会给你的事务

赋予一个时间点T，这个时间点T就是你在事务中执行select语句的时间点。如果数据库服务器为你当前事务赋予了一个时间点之后其他事务删除了数据，在你事务中是看不到这条数据被删除了。其他事务执行Insert你当前事务也看不到新增的数据，其他事务执行update你当前事务也看到更新的数据。

注意：

上述这种数据库快照一般使用了在一个事务中做数据读取操作(select).不太使用于DML(insert，update，delete)语句。

考虑如下场景：

如果你在一个事务中修改或者插入了某条记录并且提交了该事务。另外一个Repeatable read的transaction TX2是看不到他们的。虽然看不到这些更改或者新增的数据，但是TX2

可以删除刚才新增的数据(unbeliveable)，实验说明一切。

琢磨下如下实验：

在repeatable read隔离级别的session1的transaction中建立快照(执行select语句)

步骤1

mysql> select @@session.tx_isolation;+------------------------+

| @@session.tx_isolation |

+------------------------+

| REPEATABLE-READ |

+------------------------+

1 row in set (0.00sec)

mysql> start transaction;

Query OK,0 rows affected (0.00sec)

mysql> select * frommvcc;+----+-----------------+-------+

| id | name | city |

+----+-----------------+-------+

| 1 | name1-not-new-1 | city1 |

| 2 | name2-not-new | city2 |

| 3 | name3 | city3 |

+----+-----------------+-------+

3 rows in set (0.00 sec)

在另一个session2的transaction中新增一条数据

步骤2

mysql> select * frommvcc;+----+-----------------+-------+

| id | name | city |

+----+-----------------+-------+

| 1 | name1-not-new-1 | city1 |

| 2 | name2-not-new | city2 |

| 3 | name3 | city3 |

+----+-----------------+-------+

3 rows in set (0.00sec)

mysql> insert into mvcc(id,name,city) values(4,‘name4‘,‘city4‘);

Query OK,1 row affected (0.00sec)

mysql> commit;

Query OK,0 rows affected (0.00sec)

mysql> select * frommvcc;+----+-----------------+-------+

| id | name | city |

+----+-----------------+-------+

| 1 | name1-not-new-1 | city1 |

| 2 | name2-not-new | city2 |

| 3 | name3 | city3 |

| 4 | name4 | city4 |

+----+-----------------+-------+

4 rows in set (0.00 sec)

在session1中查看刚才的数据，发现查看不到，但是删除能成功。

步骤3

mysql> select * frommvcc;+----+-----------------+-------+

| id | name | city |

+----+-----------------+-------+

| 1 | name1-not-new-1 | city1 |

| 2 | name2-not-new | city2 |

| 3 | name3 | city3 |

+----+-----------------+-------+

3 rows in set (0.00sec)

mysql> delete from mvcc where id=4;

Query OK,1 row affected (0.00 sec)

这就是multi-versioned concurrency control(简称mvcc)

在下面的例子中，session A只有在sessionB commit并且session A commit之后才能看到b提交的数据。sessionB提交跟sessionA提交的这段间隔内sessionA就”错过了“sessionB的数据

Session A Session BSET autocommit=0; SET autocommit=0;

time| SELECT * FROMt;| empty set

| INSERT INTO t VALUES (1, 2);|vSELECT * FROMt;

emptyset

COMMIT;SELECT * FROMt;

emptyset

COMMIT;SELECT * FROMt;---------------------

| 1 | 2 |

---------------------

1 row in set

如果想要在在sessionA看到sessionB commit到session A commit这段时间内的数据，可以使用如下语法

SELECT * FROM t LOCK IN SHARE MODE;