CODEWITHSUNDEEP
sqlunique
Adam S
Adam S

Reputation: 1031

SQL unique : manual check vs catch exception

I'm working on a big database and i'm seeking for everything that can speed up the database. The question is : when you have an unique index on somes fields, what is the faster between make a select request to check if it's ok, or try it anyway and catch the exception if the entry already exists ?

I made some researchs but nothing conclusive. Thanks.

Upvotes: 5

Views: 1559

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95592

A manual check won't do what you think it does. (See below.)

If you check first, every insert requires two round-trips to the database. It might also require serializable transactions.

And you have to trap errors anyway. A duplicate value is just one thing that can go wrong on an insert; there are a lot of other things that can go wrong.

I say just insert, and trap the errors.

The point of a SELECT before INSERT is to determine whether a value already exists in the database. But you can't rely on that to work. Here's why.

Open two terminal sessions (for example), and connect both to your database. This table already exists. It's empty.

create table test (
  test_id serial primary key, 
  test_email varchar(15) not null unique
);

A: begin transaction;
A: select test_email 
   from test 
   where test_email = '[email protected]';
   (0 rows)

                                       B: begin transaction;

A: insert into test (test_email) 
   values ('[email protected]');
   INSERT 0 1

                                       B: select test_email 
                                          from test 
                                          where test_email = '[email protected]';
                                          (0 rows)
                                       B: insert into test (test_email) 
                                          values ('[email protected]');
                                          (waiting for lock)

A: commit;

                                       B: ERROR:  duplicate key value 
                                          violates unique constraint...

Upvotes: 7

Gurkan İlleez
Gurkan İlleez

Reputation: 1573

you have 2 choices

  1. Try to insert and if the query fails, you will catch and roll back transaction.
  2. Try to query once to check if it exists or not. If it does not exist,Insert the value.

In my opinion, first one is better because you use network connection twice if you query twice. Also select is an good option when you have really big data. In the first case you try to insert but you get DataIntegrityException. Single request and response is better than two request and two response.

Transaction Manager can handle the exception as well.

Upvotes: 3

beautifulcoder
beautifulcoder

Reputation: 11330

My understanding is try / catch exceptions will abruptly stop the flow of the program. Even when properly handled. The recommended practice is to use them apart from domain logic. An extra select shouldn't be that bad unless your database server is physically far away.

Upvotes: 1

Related Questions