Golang Postgres可重复读隔离级别不起作用

Question

My understanding of the REPEATABLE READ transaction isolation level is that once it is set, the data read through in the transaction will not change. I have used the following code to verify this:

ctx = context.Background()
tx, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable}) 
time.Sleep(5 * time.Second)
// do query on tx

While the process is sleeping, I have inserted another record through the console. But the newly inserted record has appeared in the results. Why is that so? I don't want to read the records which are inserted after the transaction has begun. I also tried:

tx, _ := db.Begin()
_, err = tx.Exec(`set transaction isolation level repeatable read;`)

But still the results are the same.

Answer 1

The snapshot of a REPEATABLE READ or SERIALIZABLE transaction is not taken at the time when the transaction is started, but when the first SQL statement runs inside the transaction.

First, this is an optimization for the case that the transaction does not run any statement at all. Second, it is the only way to allow setting the transaction isolation level after the start of the transaction, but before any SQL statement has been run with

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;