Hibernate: Prevent duplicate rows from being inserted by different threads

Question

I am using hibernate for data persistence with Spring.

Scenario is

Time- 00 Hour:01 Minute:01 Second:100 ms

• Request-1 received to persist a Person with Name "abc", Last Name "Xyz" (Consider this session 1)

• DB is looked up if the same record exist
• There is no record found
• Object gets created, Hibernate generates unique ID for the record

Time- 00 Hour:01 Minute:01 Second:102 ms

• Request-2 received to persist a Person with Name "abc", Last Name "Xyz" (Consider this session 2)
• DB is looked up if the same record exist
• There is no record found
• Object gets created, Hibernate generates unique ID for the record

Time- 00 Hour:01 Minute:01 Second:103 ms

• Session 1 persist the record with saveOrUpdate();

Time- 00 Hour:01 Minute:01 Second:104 ms

• Session 2 persist the record with saveOrUpdate();

As both sessions are generating unique identity, hibernate is treating them as separate object and persisting in DB. but this, later on, causes issues in application.

I have unique index too but that includes the Id field also, so DB is also unable to treat them as unique record.

Suggest ways to avoid duplicate insertion.

Answer 1

The easiest solution for me is to add a Multiple-Column Constraint

@Table(name = "Person", uniqueConstraints = @UniqueConstraint(columnNames = {"Name", "LastName"}))

Handle the exception in your code in order to respond to your client.

Answer 2

If I am understanding this correct, you do not have any uniquely identifiable fields which can tell the backend that incoming record is a duplicate record which is received just few seconds before.

In such case, I am afraid but there may be no direct way to handle this (I may be wrong). There can be indirect ways like

• Checking audit logs before insertions to check if record with similar data is inserted in last few seconds or minutes.
• Send a unique code from backend to frontend. frontend has to hit backend using that code. Code gets expired after single use. And check before every insertion if code sent from FE is valid

There can be many such ways and best solution has to be derived based on complexity, performance requirements etc. of your project.