1

Let's imagine my application needs to do a series of consecutive SELECTs (to different tables) to collect various bits of information from the db, and we don't want any of those tables to change while we're collecting the data. With postgresql one can use "repeatable read" or "serializable" isolation levels, however if another transaction commits changes to a table we haven't referenced yet we'll see them even if our transaction had started already, as the following sequence of actions shows:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # we'll see 11 lines for the rest of the transaction

However, if T1 accessed t before T2 did the insert, it would see 10 lines for the duration of the whole transaction:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T1: SELECT COUNT(*) FROM t;                # we'll see 10 lines for the rest of the transaction
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # still sees 10 lines etc.

With the above behavior, if we need to access multiple tables during the transaction, many of them may change in the time interval between the beginning of the transaction and the moment we access them, and we'll see those changes which we don't want to see. I understand that's the way isolation levels are supposed to work, so no explanation is needed here.

But then, is there a way to have some kind of "snapshot" starting at a given point in time? Are explicit locks needed in this case?

1
  • 1
    There are only two ways to address this: either lock all affected tables beforehand to prevent any concurrent modifications (bad), or rethink your application design (good).
    – mustaccio
    Apr 24, 2020 at 16:48

1 Answer 1

1

That is not a problem.

The serializable transaction (you should use repeatable read in this case, it performs better and does the same) effectively starts when you execute the first select, and all statements in the same transaction will see the same snapshot of the database.

So all is consistent.

3
  • You're right. My test was incomplete. I thought that what I observed for the first table was true for all tables accessed, but testing with more tables I see now that after the first operation all other tables are freezed even if the transaction has not accessed them yet. Thanks for clearing that up.
    – homer5439
    Apr 24, 2020 at 19:01
  • And yes, of course, since it's all reads the right isolation level is repeatable read.
    – homer5439
    Apr 24, 2020 at 19:02
  • 1
    And it is documented: This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction not as of the start of the current statement within the transaction. Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started.
    – pifor
    Apr 24, 2020 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.