I have multiple services both receiving the requests Sample request has 3 fields - Currency, Date, Amount
Existing table has 2 entries
Key, Currency, Date, Amount
123, USD, 1 Aug, 100
234, EUR, 1 Dec, 50
Insert Case
If no entry exists in the table with the received Currency, Date combination, a new entry is inserted. e.g. For above table, if we receive (USD, 2 Aug, 20), new entry will be inserted
Update Case
If an entry already exists in the table with the received Currency, Date combination existing entry is updated by updating the Amount field e.g. For above table, if we receive (USD, 1 Aug, 30), the existing entry will 123 will be updated and the updated amount will be 100+30 = 130
Scenario Problem
There are 3 instances of the services running in kubernetes/similar platform and below three services receives requests around same time First service receives request (USD, 1 Sep, 100) Second service receives request (USD, 1 Sep, 200) Third service receives request (USD, 1 Sep, 300)
Ideal case should be, whichever services processes first, will create the first insert entry, and the remaining two services will update the amount for that entry
Desired end result:
USD, 1 Sep, 600
But, one of the observed results is, where 2 services ends up inserting the record and depending on the application layer handling, the 3rd entry can fail as it won't find the unique entry to update the amount or it ends up updating one of the returned entry from the database based
USD, 1 Sep, 100
USD, 1 Sep, 500
Queston
How and where should this be handled? In the database using locks or at the Java application layer?
It seems to be a concurrency problem, I suppose that you are doing two queries to do this. First, you try to find the record in the database, and second, you do an update or an insert depending on if it exists or not. However, if the two requests arrive at the same time (or nearly the same time) the two make the first query and because this currency does not exist, they do the insert.
To solve this, I will add a unique constraint in the currency, as Kayaman suggests, and change the query to be an upsert (it is a combination of update and insert query that is supported in some databases) to make the query atomic so if the insert fails because there is already a record with the same currency it will do an update
Here is the documentation about how to do an upsert in Postgresql https://www.postgresqltutorial.com/postgresql-upsert/