How to avoid race condition on a sequential delete and insert in postgres

Question

I have four table called:

Participant [Columns : Id, Name]
Answer [Columns:Id, Question_id (FK of question table), Option_Id (FK of Option table), Participant_Id (FK of participant table)].
Question [Id, title]
Option [Id, title, question_id (FK of Question table)].

Participant, Question and Option records are already created by admin, Now when a participant answer to the questions we save them in every few sec (participant can answer new question or can remove answer from already answered questions).

Now while saving latest we delete exiting answers for the current participant and insert latest answers.

Now there could be a scenario where 2 thread came to save answers for same participant so both will try to delete answers and then insert. If both the thread delete at same time and then try to insert it leads to race condition and duplicate answers will be getting saved.

More details :

There are 4 existing answer for the current participant.
Thread 1 and Thread 2 came to save 6 and 7 answer record.
Thread 1 : Deleted answers (deleted existing 4 answer).
Thread 2 : Delete answers (Deleted 0 answers since already deleted by Thread 1)
Thread 1 : Insert latest 6 answers.
Thread 2 : Insert latest 7 answers.

Here current participant will have 6+7 = 13 answer which is wrong, it should be either 6 or 7.

How to deal with this with transaction or any better approach ? I do not want to use java thread lock and do not want to lock the whole table.

Some how can I take lock on participant_id of Answer table while doing delete/insert operation so that other transaction have to wait if they try to insert/update/delete with same participant id ? Looking for some solution with best performance.

I am using Postgres SQL and JPA. I am ready to use normal SQL for both delete and insert if required.

fphilipe · Accepted Answer · 2019-04-23 04:50:43Z

You could acquire an explicit lock on the participant:

BEGIN;
SELECT FROM Participant WHERE Id = $PARTICIPANT_ID FOR NO KEY UPDATE;
DELETE FROM Answer WHERE Participant_Id = $PARTICIPANT_ID;
INSERT INTO Answer (Question_id, Option_Id, Participant_Id)
    VALUES ($QUESTION_ID, $OPTION_ID, $PARTICIPANT_ID);
COMMIT;

This way, any other connection trying to do the same will have to wait until this transaction is completed.

To convince yourself, try this with two psql sessions. In the first, you type:

BEGIN;
SELECT FROM Participant WHERE Id = $PARTICIPANT_ID FOR NO KEY UPDATE;

In the second session, you'll notice that a regular SELECT still works:

SELECT * FROM Participant WHERE Id = $PARTICIPANT_ID;

But acquiring the same lock in the second session will hang:

SELECT FROM Participant WHERE Id = $PARTICIPANT_ID FOR NO KEY UPDATE;

It will wait until you COMMIT or ROLLBACK the transaction in the first session, thus guaranteeing that there is only one thread at a time holding that lock.

If you prefer to fail early in case the lock is not available rather than waiting to acquire the lock, you can use NOWAIT. Try this now in the second session while the transaction is still ongoing in the first session:

SELECT FROM Participant WHERE Id = $PARTICIPANT_ID FOR NO KEY UPDATE NOWAIT;

You'll get an immediate error:

ERROR: could not obtain lock on row in relation "Participant"

See the PostgreSQL docs about explicit locking for more information.

Collectives™ on Stack Overflow

How to avoid race condition on a sequential delete and insert in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related