Will a transaction see inserts from another concurrent transaction if they are in Repeatable Read isolation level in Postgresql?

If during execution of transaction A, a concurrent transaction B adds a row that fits the search criteria of transaction A and commits, what will happen when transaction A commits? Will it include the new row, or will the transaction fail?

Assuming they are run in Repeatable Read isolation level in Postgresql.

Answer

The transaction A won’t fail. It won’t see the data inserted by transaction B.

Demo:

create table accounts (id bigserial primary key, balance bigint);
insert into accounts(balance) values (50), (100), (200);

Transaction A:

begin transaction isolation level repeatable read;

select * from accounts where balance >= 100;
 id | balance 
----+---------
  2 |     100
  3 |     200
(2 rows)

Transaction B:

begin;
insert into accounts(balance) values (300);
INSERT 0 1
commit;

Transaction A:

select * from accounts where balance >= 100;
 id | balance 
----+---------
  2 |     100
  3 |     200
(2 rows)

No (4, 300) row.

(tested on PostgreSQL 11.2)

Note that PostgreSQL has stronger guarantees on REPEATABLE READ isolation level. It prevents phantom reads.

From the documentation:

The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions. (However, the query does see the effects of previous updates executed within its own transaction, even though they are not yet committed.) This is a stronger guarantee than is required by the SQL standard for this isolation level

From the Table 13.1. Transaction Isolation Levels:

Phantom reads are allowed at REPEATABLE READ isolation level but not in PG

See also:

• How to produce phantom reads

Update

If the statement

update accounts set balance = balance + 30 where balance >= 100;

is issued as the last statement of transaction A only 2 rows will be updated because from the point of view of transaction A there are only two rows satisfying the balance >= 100 predicate:

update accounts set balance = balance + 30 where balance >= 100;
UPDATE 2

After a commit:

commit;
COMMIT
select * from accounts;
 id | balance 
----+---------
  1 |      50
  4 |     300
  2 |     130
  3 |     230
(4 rows)

Only rows returned by this

select * from accounts where balance >= 100;

statement are updated (excluding the row inserted by transaction B)

Note the transaction A will fail if it tries to update a row changed by another committed concurrent transaction:

A

begin transaction isolation level repeatable read;
BEGIN
select * from accounts where id = 1;
 id | balance 
----+---------
  1 |      50
(1 row)

B

begin;
BEGIN
update accounts set balance = balance + 10;
UPDATE 3
commit;

A:

-- By some logic based on the fact that the balance is 50 we decided to update it to 60
-- balance is updated by committed concurrent transaction 
update accounts set balance = 60 where id = 1;
ERROR:  could not serialize access due to concurrent update

The error is expected. From the documentation:

If the first updater rolls back, then its effects are negated and the repeatable read transaction can proceed with updating the originally found row. But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message

ERROR: could not serialize access due to concurrent update because a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.

It is expected that applications will retry failed transactions:

When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.