16

Repeatable read is defined as

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.

Which seems very similar to snapshot isolation.

How is repeatable read different from the Snapshot isolation level?

Anurag Sharma
  • 2,409
  • 2
  • 16
  • 34

2 Answers2

25

"Snapshot" guarantees that all queries within the transaction will see the data as it was at the start of the transaction.

"Repeatable read" guarantees only that if multiple queries within the transaction read the same rows, then they will see the same data each time. (So, different rows might get snapshotted at different times, depending on when the transaction first retrieves them. And if new rows are inserted, a later query might detect them.)

ruakh
  • 175,680
  • 26
  • 273
  • 307
  • 1
    From https://www.cockroachlabs.com/blog/consistency-model/ I understand that repeatable read allows phantoms meaning that "if a transaction performs a query asking for rows matching a condition twice, the second execution might return more rows than the first. For example, something like select * from orders where value > 1000 might return orders (a, b, c) the first time and (a, b, c, d) the second time". Multiple tables appear not to be required. – Javier Oct 23 '20 at 20:57
  • 1
    @Javier: Thanks for your comment! I see how my parenthetical note could have been confusing. I've adjusted it now, and incorporated your example, which is very helpful. :-) – ruakh Oct 23 '20 at 21:19
2

Snapshot isolation is called "Repeatable Read" in PostgreSQL and in MySQL. It is called "serializable" in Oracle DB. IBM DB2 uses "repeatable read" to refer to serializability.

SQL standard doesn't have the concept of snapshot isolation that was defined years later, this is the reason for the naming confusion.

As a result, nobody really knows what repeatable read means. But, it's reasonable to assume that they are the same thing.

Nathan B
  • 1,625
  • 1
  • 17
  • 15