mysqlpostgresqltransactionsisolation-level

When sanpshot is taken into consideration in transactions?

When is the snapshot actually taken in transactions? On BEGIN TRANSACTION or on the first operation?

I've been experimenting with transactions in both MySQL and PostgreSQL using the REPEATABLE READ isolation level, and I'm noticing something unexpected:

I start two transactions, T1 and T2. After starting T1, I make changes in T2 (such as updating rows), and I can still see those changes in T1 until I perform the first query (read or write) in T1. It seems like the snapshot isn’t created at BEGIN TRANSACTION, but only after the first operation in the transaction.

Is this the expected behavior in MySQL and PostgreSQL? When exactly does the snapshot isolation begin in a transaction?

Solution

  • For MySQL, https://dev.mysql.com/doc/refman/en/innodb-transaction-isolation-levels.html has the answer:

    Consistent reads within the same transaction read the snapshot established by the first read.

    PostgreSQL is similar. https://www.postgresql.org/docs/current/transaction-iso.html states:

    ... a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction...