undefined | Better HN

0 pointsDiggsey3y ago0 comments

One thing that can be surprising is that for "REPEATABLE READ", not all "reads" are actually repeatable.

There are at least two ways (that I'm aware of) that this can be violated. For example, if you run an update statement like this:

    UPDATE foo SET bar = bar + 1

Then the read of "bar" will always use the latest value, which may be different from the value other statements in the same transaction saw.

0 comments

kerblang3y ago

Not sure what you're claiming here...

Repeatable read isolation creates read locks so that other transactions cannot write to those records. Of course our own transaction has to first wait for outstanding writes to those records to commit before starting.

Best as I know the goal is not to prevent one's own transaction from updating the records we read; the read locks will just get upgraded to write locks.

DiggseyOP3y ago

> Repeatable read isolation creates read locks so that other transactions cannot write to those records.

No it doesn't, that's just one possible implementation strategy. Postgres for example does not do this.

> Best as I know the goal is not to prevent one's own transaction from updating the records we read;

I'm talking about updates from other transactions. In postgres with REPEATABLE READ, the following transaction can be executed concurrently by two clients:

    BEGIN
    SELECT bar FROM foo WHERE id = 1;  -- Returns 0
    UPDATE foo SET bar = bar + 1 WHERE id = 1;
    COMMIT

Both clients can see a value of "0" from the first SELECT, but after both COMMIT, the value of "bar" will be "2". ie. the "read" of "bar" in "bar = bar + 1" for one of the transactions does not use snapshot isolation.

j / k navigate · click thread line to collapse