1

I've noticed when two processes (let's call them A and B) insert a record into the same table in Postgresql and don't commit those changes, the 2nd (later process, B) blocks until the 1st process (A) commits its changes. However, it seems as if when using an insert-into-select instead of a plain insert, the insert-into-select query is not entirely blocked and the result of the select is from before the 1st process ran.

Reading the docs on isolation levels, I think that this is kind of expected. I am now wondering what/if there is anything that can be done on the database level to allow the 2nd process to synchronise to the commit of the 1st process. What's the common (recommended) way to proceed in such a scenario?

Here are some queries/diagrams that should help to illustrate the problem.

-- initial state
create table foo
(
  id integer not null,
  parent_id integer
)

insert into foo (id, parent_id) values (1, NULL)
insert into foo (id, parent_id) values (2, 1)

Process A

  1. insert into foo (id, parent_id) (select 100, max(id) from foo)

  2. ... do some stuff

  3. commit

Process B

  1. insert into foo (id, parent_id) (select 200, max(id) from foo)

  2. ... do some stuff

  3. commit

Sequence of events if A and B were run independently (Process A starts with, does runs the insert query (1), does stuff (2) and finally commits much later (3). Process B is interleaved with that, but the important thing is that (1) happens after A's insert query).

A ====|1|.... 2 ....|3|
B ========|1|2|3|

Resulting sequence, because B.1 happens after A.3 (A's commit)
B ====================|1|2|3|

The table after A and B

id  | parent_id
----+-----------
1   | NULL
2   | 1
100 | 2
200 | 2  <= would love to see 100 here, but the `max(id)` is from before A.1 ran

The behaviour was the same for isolation levels 'READ COMMITTED' and 'REPEATABLE READ'.

3
  • What happens if you rollback A? Does B get parent_id = 2?
    – Luan Huynh
    Jun 17, 2021 at 8:21
  • Oh sorry, I made a mistake. parent_id is 2, not 1
    – orange
    Jun 17, 2021 at 11:00
  • "B) blocks until the 1st process (A) commits its changes" I do not see that blockage happening in your example, and do not expect it to happen.
    – jjanes
    Jun 17, 2021 at 12:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.