Similarities and differences of snapshot reads at the RC and RR isolation levels

In the last article I introduced the implementation principle of MVCC, now let’s look at the RR and RC level of snapshot read

RC (read committed) We all know that RC can handle dirty reads (read uncommitted data), but how does it do that?

When the transaction of transaction A is completed, transaction B reads the snapshot again, and we can find that the read data at this time is A =400. So why is that? In fact, when transaction A has not committed, transaction B’s first snapshot Read View, transaction A is active transaction, so transaction B cannot Read the data modified by transaction A. However, after transaction A commits the transaction, transaction B reads the snapshot again, and the Read View is reproduced. Transaction A is no longer an active transaction, and transaction B reads the data submitted by transaction A.

RR (Repeatable read) Repeatable read resolves dirty and unrepeatable reads. Let’s look at the following two transactions:

We can see that the first snapshot read from transaction B is 500, and then after transaction A commits, the next snapshot read from transaction A is still 500, which guarantees that the data read from transaction B is the same. Why?

In the previous article, we said that each snapshot Read of a transaction is accompanied by the creation of a corresponding Read View. When transaction B makes its first snapshot Read, the Read View is created. Since transaction A is an active transaction, the changes made by transaction A are not visible to transaction B (see next article for more details). When transaction A commits the transaction, transaction B reads the snapshot again. Instead of creating A new Read View, transaction B uses the same Read View created by transaction B’s first snapshot Read, so it is clear that A still reads 500.

In the RC level, each snapshot Read is regenerated, while in the RR level, the Read View generated from the first snapshot Read is used.