Unique constraint (or) Insert if none exists, which is safer - multi-threaded environment

Question

I have seen in many applications they use a unique constraint, and in some applications, instead of using a unique constraint, they use insertion if the request does not exist

In a multi-threaded environment, where there is a possibility that several threads will try to insert at the same time ... maybe the same second or millisecond too ...

Then what would be the best option? Go to a unique constraint or go to an insert if it does not exist.

Thanks.

Answer 1

I would prefer to use unique keys because they are transparent and prevent duplication in the source. (i.e. you cannot enter an error in your code to add duplicate data or another application / person cannot add duplicate data even by directly editing the table data)

For multi-threaded applications, if the code / requests are poorly written, this can lead to duplication, but a good implementation can guarantee uniqueness. (Single transaction, check for availability and insertion in the same request, and not in different requests, non-automatic commit, etc.)

On the other hand, the disadvantage of a unique key constraint would be the database overhead. (Really can't think of any other flaws)

It happened:

Option 1: Based on a database whose task is to process data and related constraints, where most implementations are really good in both performance and reliability.

Option 2: manually write a unique constraint verification code, which ultimately performs a unique verification using database queries. And this does not prevent data duplication if there are other record clients in the same database table.

If your primary concern is performance, then it depends on your data and db. You need to do a test.

Otherwise, the choice will be clear.