-1

Suppose that we want to insert a record in some table. But in order to be allowed to do that, that table must not contain any record with duplicated values in some fields in such a way that database primary keys are not enough for doing that control and it must be done by the application's code. If the code for inserting a record looked like this...

check duplicates
if no duplicates:
  insert the record
else:
  show the user a error

That code would be wrong because two different threads could make the check of duplicates at the same time, then pass the check, then insert the same record, producing a situation where there are duplications so that the table state is now inconsistent.

As the code is a web application made in Java, I guess that it would be enough to synchronize the critical section with the same static object so that any user that makes the execution flow to get into the critical section must wait for another one that has previously got into that section. But, is that enough? Is there a more elegant way for doing that?

user3289695
  • 740
  • 1
  • 9
  • 20
  • That's definitely something you need to do in the database. Doing it in code would be pure madness. Madness! – Kayaman Feb 08 '18 at 07:49
  • Can a MySQL stored procedure do the first check and insertion in such a way that two concurrent connections cannot do it at the same time? – user3289695 Feb 08 '18 at 07:50
  • Why do you think you can't prevent it using constraints? – Kayaman Feb 08 '18 at 07:51
  • 1
    Can't you just create a unique index which contains all the fields that you want check? – dunni Feb 08 '18 at 07:51
  • Database constraints are not suitable for this purpose. There are two fields, one is a name and the other is a boolean. There must not be two records with same name and the boolean field set to true, but there may be two records with same name if one has the boolean field set to true and the others to false. How can you control that by constraints? – user3289695 Feb 08 '18 at 07:54
  • 1
    You create a unique index with both columns in it. That does exactly the thing that you want. It will not allow two entries with the same values in the two columns, but will allow entries where at least one field is different. – dunni Feb 08 '18 at 07:58

2 Answers2

0

You can use database triggers for that. The correct one to use in this case is "Before Insert" trigger. If the use case is simpler, another option would be to use checks and constraints.

Third option would be to do it in the java code and synchronizing the section like you described would work too.

Janar
  • 2,623
  • 1
  • 22
  • 32
  • But, what would happen if two concurrent connections try to insert a record and the trigger is executed for the two connections at the same time? Would it be possible that the two connections pass the check at the same time and then insert the same record two times? – user3289695 Feb 08 '18 at 08:04
  • Doing it in Java code would be a really bad choice. – Kayaman Feb 08 '18 at 08:22
  • @user3289695 You can use locks for that. SELECT .. FOR UPDATE would probably be the best choice here. More information can be found here https://stackoverflow.com/questions/24266838/triggers-and-table-lock-in-mysql – Janar Feb 08 '18 at 08:32
0

According to the clarification from the question comments, a unique index is exactly what's needed here. Create a unique index with the name column as well as the boolean column in it. It will then not allow two entries where both columns have the same values.

dunni
  • 43,386
  • 10
  • 104
  • 99
  • The problem is that two records with the same name and the boolean column set to false are indeed allowed. That unique index wouldn't allow this situation. – user3289695 Feb 08 '18 at 08:00
  • Well then you could use a trigger as Janar mentioned in his answer. – dunni Feb 08 '18 at 08:02