Prevent duplicated rows in MySQL table in scalable environment

Question

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):

• Make a select first and, if not results, perform insert (but in this case I have no guarantee that other instance of populating application will not add the row meanwhile - race condition)
• using locks on the table
• handling exceptions when inserting data vialoting constraint limitations - not nice cause generates hibernate errors in logs

Questions:

• What other options should I consider in such case?
• What is the most common approach to prevent duplicated rows in multi-instance environments?

Answer 1

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.