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

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.

CodePudding user response：

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/

CodePudding user response：

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;