Can SELECT get different results within one transaction using Read Committed Isolation Level?

Question

So here is full paragraph from postgresql 9.6 docs about Read Committed Isolation Level:

Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. 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. 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.

---

So basically:

SELECT query sees only data committed before the query began and never sees changes committed during query execution by concurrent transactions.

But in last sentence it states that:

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.

For me it looks contradictory. Could someone elaborate that? How exactly two SELECT queries could see different data within one transaction? Isn`t transaction isolated?

Answer 1

Yes, that's true. To avoid such cases you need to use a higher isolation level: "repeatable read". Or even "serializable" if you need your transactions to be completely isolated. Just keep in mind that higher isolation pays higher cost by means of performance.

Here you can find detailed explanation: https://www.postgresql.org/docs/9.1/static/transaction-iso.html

So here is an example how could it happen:

1. Connection1 opens transaction and reads a row from Table1 with "select *"

2. Connection2 updates the same row and commits

3. Connection1 reads the same row again and gets updated data

The isolation level is "read committed", so literally everything commited becomes visible to other connections.

If you can not use a higher isolation level for some reason, there is a way to prevent such "unexpected" update from happening: your Connection1 can use "select ... for update" instead. This will effectively lock the row until transaction of Connection1 commits or rolls back. So Connection2 will wait for this commit or rollback to be able to update the row.

Answer 2

There is no contradiction. Two consecutive SELECT statements within a transaction may fetch different results. Consider this - you begin a transaction, then issue
select * from emp; You get 2 records. Another session inserts a record into emp and commits. In the first session, you again issue select * from emp; You get 3 records. This is expected behavior at READ COMMITTED isolation level.

Sample code

tmp=# begin ; 
BEGIN
tmp=# select * from emp;
 id 
----
  1
  2
(2 rows)

tmp=# select * from emp;
 id 
----
  1
  2
  3
(3 rows)

tmp=# commit;