I would like to clarify answer for this question because marked statements are contradictory.
Can SELECT get different results within one transaction using Read Committed Isolation Level?
Based on official documentation:
When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) 1. sees only data committed before the query began; it never sees either uncommitted data or changes committed by concurrent transactions during the query’s execution. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. 2. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes after the first SELECT starts and before the second SELECT starts.
Let’s assume that statement 2 is correct, what than statement 1 means? How can two successive SELECT commands can see different data, even though they are within a single transaction if we just learned that
it never sees changes committed by concurrent transactions during the query’s execution
Does statement 1 means if we do 1 big complex query with joins, then this query and it’s joined tables will come from ‘snapshot’ (be isolated)?
>Solution :
The confusion is around the term "the query". It means the specific select statement, not the transaction. A commit made by another transaction during your transaction may still become visible to a later select within your transaction.