CODEWITHSUNDEEP
mysqlspringjpamicroservices
radekEm
radekEm

Reputation: 4647

Prevent duplicated rows in MySQL table in scalable environment

I have an Employees MySQL table in my project and 10 instances of a SpringBoot application each of which can populate data to it. Table looks as follows...

ID  |  FirstName  | LastName
------------------------------ 
1   |  Aaa        | Aaaa 
2   |  Bbb        | Bbbb 
3   |  Ccc        | Cccc

I want to ensure, it is impossible to insert any other entity with already existing FirstName/LastName pair.

Options I can see is (but not sure if correct):

Questions:

Upvotes: 3

Views: 37

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

I would use exception handling. This is an ordinary and common practice in programming. Your question was what is the most common solution, and this is it.

If you want to minimize the number of exceptions, then use a select to check for the name first. That creates a race condition, as you know, so you would still have to catch the exception. But it should avoid most of the conflicts.

I don't know why Hibernate would log anything if you handle the exception. That sounds unlikely to me, but I am not a Hibernate developer, so I don't know if there's a way to prevent that logging.

Using a table lock would also avoid the exception, but it restricts concurrent inserts, so it's not a good solution if you have a high traffic rate.

Upvotes: 1

Related Questions