数据库事务隔离级别之 Repeatable Read （可重复读）

可重复读隔离级别（Repeatable Read Isolation Level）

一、可重复读（Repeatable Read）的概念

可重复读取隔离级别 只能看到（当前）事务开始之前提交的数据；它从不看到未提交的数据或并发事务在（当前）事务执行期间提交的更改。

The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.

二、Read Committed 隔离级别下的【不可重复读】发生场景举例

1、小明很开心自己考了69分，于是他连接到数据库查询自己的成绩来炫耀给小伙伴。
2、
1）接着小明觉得还不尽兴，于是又查一次。
2）不幸的是，小明的班主任王老师复查试卷后，发现小明的成绩多加了10分，于是他连接到数据库来修改小明的成绩到 59分，王老师为了继续检查，并未提交事务。
3）那么，小明的第二次查询结果还是69分。他不知道自己其实是不及格的。除非等到王老师的更新操作提交（commit）以后，他再进行一次查询。
4）此时同一事务中，小明的第三次查询结果与第二次查询结果不一致，即为：不可重复读

说明：如果小明已经读到数据，但是当前事务未结束，在 Repeatable Read 隔离级别下，王老师是获取不到对数据的修改权限的。因为已经被小明读到了。Repeatable Read 会保证这两次读取到的数据是一样的，意味着对读取到的数据限制其它事务对其修改。
但是在 Read Committed 隔离模式下，小明的事务允许王老师的事务对其读取到的数据进行修改，这样就导致了同一事务中读取的数据不一致（不可重复度）。

三、读已提交 vs 可重复读 vs 序列化

【读已提交】：是一种隔离级别，可确保读取当前已提交的任何数据。它只是限制读者看到任何中间的、未提交的、“脏”的阅读。它不承诺如果事务重新发出读取，将找到相同的数据，数据在读取后可以自由更改。

【可重复读】：是更高的隔离级别，即除了保证【读已提交】级别之外，还保证读到的任何数据都不能被改变，即：如果事务再次读到同样的数据，就会发现之前读到的数据在原地，不变，并且可以阅读。

【序列化】：提供了更强大的保证：除了所有可重复读取保证之外，它还保证后续读取不会看到新数据。

Read committed is an isolation level that guarantees that any data read was committed at the moment is read. It simply restricts the reader from seeing any intermediate, uncommitted, 'dirty' read. It makes no promise whatsoever that if the transaction re-issues the read, will find the Same data, data is free to change after it was read.

Repeatable read is a higher isolation level, that in addition to the guarantees of the read committed level, it also guarantees that any data read cannot change, if the transaction reads the same data again, it will find the previously read data in place, unchanged , and available to read.

Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on referenced rows for update and delete actions. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable read.
可重复读取——这几乎是最严格的隔离级别。事务在它引用的所有行上持有读锁，并在被引用的行上写锁以进行更新和删除操作。由于其他事务无法读取、更新或删除这些行，因此它避免了不可重复读取。

serializable, makes an even stronger guarantee: in addition to everything repeatable read guarantees, it also guarantees that no new data can be seen by a subsequent read.

ReadUncommitted: Transaction B can read uncommitted data from Transaction A and it could see different rows based on B writing. No lock at all
ReadCommitted: Transaction B can read ONLY committed data from Transaction A and it could see different rows based on COMMITTED only B writing. could we call it Simple Lock?
RepeatableRead: Transaction B will read the same data (rows) whatever Transaction A is doing. But Transaction A can change other rows. Rows level Block
Serialisable: Transaction B will read the same rows as before and Transaction A cannot read or write in the table. Table-level Block