Unique Constraint (Or) Insert if not exist which is Safer - Multi-thread environment

Question

I saw in many applications they are using unique constraint as well as in some applications instead of using unique constraint they are using insert if not exist query

In a multi-threaded environment where there is a chance of multiple thread trying to insert at the same time..may be same second or millisecond too...

In such a case, which option would be the best ?? Going for a unique constraint or going for insert if not exist.

Thanks.

Answer 1

I would prefer using unique keys, because they are transparent and they prevent duplication at the source. (i.e you cannot introduce a bug in your code to add duplicate data or another application/person cannot add duplicate data even by directly editing table data)

For multi threaded applications, if the code/queries are written poorly it could result in duplicates, but a good implementation could guarantee uniqueness. (Single transaction, existence check and insert in the same query, not in different queries, no-auto commit etc.)

On the other hand, downside of unique key constraint would be the database overhead. (Cannot think of any other downsides actually)

So it comes to this:

Option 1: Rely on the database, whose task is to handle data and related constraints, where most implementations are really good both performance-wise and reliability-wise.

Option 2: Manually write unique constraint checking code, which eventually does the unique check using DB queries. And it does not prevent duplicate data in case there are other write-clients to the same database table.

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

Otherwise, the choice is clear.