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.

What's your personal effort to solve this issue? Why do you have an unique index that contains auto-generated ID? What's the reason of such index? — Slavkó Medvediev
– Slavkó Medvediev, Commented Sep 23, 2020 at 17:43

Sridhar Patnaik · Accepted Answer · 2020-09-23 17:58:08Z

1

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.

answered Sep 23, 2020 at 17:58

Sridhar Patnaik

1,1181 gold badge16 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ravi Over a year ago

Yes after few days of thought process even I could not find any direct way to handle the case. Thanks for putting up the possible work around. Will give it a try for audit suggestion.

LoVas2 · Accepted Answer · 2022-11-10 22:11:41Z

0

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.

answered Nov 10, 2022 at 22:11

LoVas2

1

Collectives™ on Stack Overflow

Hibernate: Prevent duplicate rows from being inserted by different threads

Scenario is

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Scenario is

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related