CODEWITHSUNDEEP
javamysql
Aryan R
Aryan R

Reputation: 233

In mysql is it advisible to rely on uniqueness constraints or to manually check if the row is already present?

I have a table:

userId | subject

with uniqueness constraint on both combined.

Now I am writing thousands of rows to this table every few minutes. The data stream is coming from a queue and it might repeat. I have to however make sure that there is only one unique combination of userId,subject in the table.

Currently I rely on mysql's uniqueness constraint which throws as exception.

Another approach is run a SELECT count(*) query to check if this row is already present and then skip it if need be.

Since I want to write on an average 4 rows per second what is advisable.

Programming language: Java

EDIT:

Just in case I am not clear the question here is whether relying ton MYSQL to throw an exception is better or running a select query before insert operation is better in terms of performance.

I thought a select query is less CPU/IO intensive than a INSERT query. If I run too many INSERTS wouldn't that create many locks ?

Upvotes: 1

Views: 177

Answers (2)

Aaron Kent
Aaron Kent

Reputation: 628

MySQL is ACID and employs transactional locking, so relying on its uniqueness constraints is very standard. Note that you can do this either via PRIMARY KEY or UNIQUE KEY (but favour the former if you can).

Upvotes: 3

Christian Kuetbach
Christian Kuetbach

Reputation: 16060

A unique constraint is unique for the complete committed dataset.

There a several databases which allows to set "transaction isolation level". 

userId    subject
A         1
B         2
-------------------------
A         2
A         3

The two rows above the line are committed. Every connection can read these lines. The two line under the line are currently been written within your transaction. Within this connection all four lines are visible.

If another thread / connection / transaction tries to store A-2 there will be an exception in one of the two transaction (the first one can commit the transaction, the second one can't).

Other isolation level may fail earlier. But it is not possible to violate against the Unique-key constraint.

Upvotes: 2

Related Questions