How to handle two insert on conflict do update parallel postgres queries

Question

My android device calls an endpoint in spring boot. When two devices call in parallel - the insert is not completed for the first call, the second call reaches the db meanwhile. The first call seems to be generating the primary key and the second call sees the conflict and hence tries to update. The first call is supposed to insert, subsequent calls should increment the value by 1. The insert is not completed so the second call tries to update the value. Hence it updates the value as null. Subsequent calls do value + 1 hence they update null again. How do I handle this scenario to make sure one call locks the row or how do I solve this problem?

insert
    into
    table1 (primary_key,
    quantity)
values(:primary_key,
:qty) on
conflict primary_key do
update
set
    quantity = (
    select
        coalesce(quantity, 1) + :qty
    from
        table1
    where
        primary_key = :primary_key)
where
    primary_key = :primary_key;

Note - :qty will be 1 and :primary_key will be the key passed on from the code.

Yes I do see the behavior still. Both requests are hitting at the same time and 1 of it inserts null. — Shyam Sundar N, Oct 22 '21 at 09:59

score 0 · Answer 1 · answered Oct 22 '21 at 05:47

0

Make your database insert action a transaction (what is a database transaction?), in short it either finishes everything or nothing.

You didn't post your back-end code but for reference it can be done in spring boot like this https://spring.io/guides/gs/managing-transactions/

answered Oct 22 '21 at 05:47

eth4io

288
4
15

I did have it earlier as a transaction using the @transactional annotation. Here the problem is not between everything vs nothing. The insert on conflict do update is executed. But the query puts a null value. – Shyam Sundar N Oct 22 '21 at 10:02

score 0 · Answer 2 · answered Oct 22 '21 at 14:14

0

The explanation is that your subquery doesn't find a row yet, because it is not yet visible to the transaction. Hence the result is NULL.

Avoid that race condition with the much simpler

INSERT INTO table1 (primary_key, quantity)
VALUES (:primary_key, :qty)
ON CONFLICT (primary_key)
DO UPDATE
SET quantity = coalesce(table1.quantity, 1) + EXCLUDED.quantity;

answered Oct 22 '21 at 14:14

Laurenz Albe

209,280
17
206
263

This is a good solution. However, I resolved it by removing the sub query. The select in sub query is not aware of the insert that is not committed yet. So, this is the solution which works for us. `insert into table1 (primary_key, quantity) values(:primary_key, :qty) on conflict primary_key do update set quantity = table1.quantity + :qty where primary_key = :primary_key;` – Shyam Sundar N Oct 28 '21 at 12:08

How to handle two insert on conflict do update parallel postgres queries

2 Answers2