Search code examples
sqldatabaseisolation-level

What is the difference between "repeatable read" and "snapshot isolation"


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?


Solution

  • "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.)