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.