CODEWITHSUNDEEP
sqlpostgresqltransactions
Martin
Martin

Reputation: 4222

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

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?

Upvotes: 4

Views: 3019

Answers (3)

Gui Meira
Gui Meira

Reputation: 885

Adding an answer here 6 years after this question was asked because I got here trying to understand why the Postgres documentation seems to contradict itself.

The source of the confusion here is in the use of the words query and transaction.

The docs state that when a SELECT query starts, it sees only data that was committed before that query began. Note that it's referring to just the SELECT query itself, not to the whole transaction. One transaction may contain several queries.

So suppose you're running the query below in a transaction with read-committed isolation level:

SELECT name FROM users;

Suppose the users table is very large and your query is really complicated, so it takes a while to complete. While the SELECT is running, some other transaction does this:

BEGIN;
INSERT INTO users(name) VALUES ('John');
COMMIT;

What the Postgres docs are saying is that your first SELECT will never include 'John' in the results because the query started before the second transaction committed. If you start a new query (even if you're within the same transaction), then you'll see 'John' in the results.

If you cannot have 'John' show up in any SELECT during a transaction, you'll need to use a higher isolation level (at the cost of reduced performance).

Upvotes: 2

user3714601
user3714601

Reputation: 1271

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.

Upvotes: 6

Jayadevan
Jayadevan

Reputation: 1332

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;

Upvotes: 2

Related Questions