Issue
I have 2 instances of Spring application which has a method with the @Transactional annotation. The method receives a value and checks if the DB has a row with this value and simply inserts if it doesn't exist.
When I'm firing 4 concurrent calls to this method with a new value, I expected to see only 1 row to be inserted into the DB. However, I'm observing that anywhere between 1 and 3 rows are getting inserted.
How can I avoid inserting multiple records?
Solution
How can I avoid inserting multiple records?
I would put a unique constraint on the table. They exist exactly for that purpose are reliable and tuned for performance for dozens of years.
If you insist on doing this using @Transactional you'd want to set the isolation level to SERIALIZABLE. I'm not sure all databases support that so make sure to run some tests.
This puts sever limits on throughput, since it almost means that only one transaction at a time can proceed at any given time.
And finally, I'm not sure how that interacts with other transactions with other isolation levels.
Answered By - Jens Schauder
Answer Checked By - Gilberto Lyons (JavaFixing Admin)
