0

I would like to know if there is a real case scenario in which race condition problems can actually occur on an insertion query. So I have a table "User" with the following fields:

User:
iduser | idcompany | name | email

I use a composite primary key for this table that is (iduser, idcompany). None of these fields is set to AUTO_INCREMENT. I get the value of the field "idcompany" through a session variable, so there's is not a real problem in this. However, I use a getNextUserId() function to get the next valid iduser value through a select query like this: 

SELECT MAX(iduser) + 1 AS next_iduser FROM User WHERE idcompany = {myCompanyId};

I wonder if there is any case in which a duplicate combination of (iduser, idcompany) could be inserted in the database because of race condition and if so how is this scenario possible. Doesn't MySQL lock the table on insertion? Wouldn't a duplicate combination of (iduser, idcompany) simple be rejected or I could really have a duplicate primary key in my table? I am aware of strategies to totally prevent race condition like using AUTO_INCREMENT primary key, using SQL transactions or manually lock the "User" table but I would like to understand the mechanism behind a possible race condition in this case and what are the real problems in this implementation. Right now, none of these fields is required to be unique in my table for obvious reasons, but I would like to know if a UNIQUE constraint on iduser for example would alter the scenario I am facing and why does this happen.

iiirxs
  • 4,493
  • 2
  • 20
  • 35
  • It locks during insertion, but not while 10 users from the same company all try to register and you have to run the SELECT to find the next ID to use 10 times without a lock. You should really be doing this in a transaction, but that assumes you are using PDO or MYSQLI and the database tables are INNODB – RiggsFolly Aug 17 '16 at 13:43
  • 1
    You might like to take a look at `SELECT..... WITH LOCK` [here in the manual](http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html) – RiggsFolly Aug 17 '16 at 13:46
  • As I explicitly say in my question I am not searching for a solution to my problem, I am trying to understand the possible consequenses in my implementation. Bill Karwin refers in his book "SQL Antipatterns" that this practice would lead to race conditions so I'm trying to figure out which are the possible scenarios in this case. – iiirxs Aug 17 '16 at 13:50
  • Can you add some context from the Bill Karwin quote? – Drew Aug 17 '16 at 14:01
  • "Some people allocate a value for a new row by taking the greatest value currently in use and adding one. SELECT MAX(bug_id) + 1 AS next_bug_id FROM Bugs;. This isn’t reliable when you have concurrent clients both querying for the next value to use. The same value could be used by both clients. This is called a race condition." quoting from Bill Karwin – iiirxs Aug 17 '16 at 14:15
  • 1
    And that speaks toward the @RiggsFolly comment. Use an [intention lock](http://stackoverflow.com/a/38079598). No one would wisely use max. – Drew Aug 17 '16 at 14:18
  • @iiirxs Interesting compartmentalisation there. You know you have a problem, but you are not looking for a solution to the consequence of your design. _Should we name this the Ostrich pattern_ – RiggsFolly Aug 17 '16 at 14:30
  • @RiggsFolly Hahaha, I understand your intention to help but this design wasn't selected by me and it's not up to me to decide, so I'd like only to understand the possible problems that it could cause, just to be aware of them. Thank you in any case! – iiirxs Aug 17 '16 at 14:35
  • 1
    @RiggsFolly my interpretation of the question is that the OP wanted to undertand the implications of a certain design. The question clearly states that the OP is aware of other solutions to avoid this race condition. – Shadow Aug 17 '16 at 14:36
  • @iiirxs Oh right. I would be tempted to _Bring this up somewhere_ so if you get an issue, you dont have to wait to ID it. This sort of probelm tends to meterialise somewhere completely unassociated in a system. And therefore takes more time to find and fix and therefore give a larger problem to fix when it is eventually discovered. Like a big marketing push to get people from one company to sign up.It wont instantly be obvious what the issue is because it might not be noticed until a month end marketing report goes BANG – RiggsFolly Aug 17 '16 at 14:39
  • 1
    @RiggsFolly there are multiple questions here in the last paragraph. Perhaps you can craft an answer. – Drew Aug 17 '16 at 14:47

1 Answers1

0

You wrote you had a composite primary key on iduser, idcompany fields. This constraint will prevent the table from having duplicate iduser, idcompany pairs. The worst that could happen without locking is that the a creation of a user will be prevented by the violation of the primary key.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • So I could never have a duplicate pair even if race condition occurs? Can be my current implementation an acceptable one? – iiirxs Aug 17 '16 at 13:45
  • 1
    This specific race condition can be perfectly handled by the composite primary key (or unique index) constraint. – Shadow Aug 17 '16 at 14:05
  • There are only 2 meaningful comments in the book as I see it about race conditions. One is about the use of `max()` . The other is in an FAQ about the re-use of an AI in a rollback. So I think Shadow's answer is fine here. – Drew Aug 17 '16 at 14:10
  • Shadow, I took the liberty to edit your answer. Modify accordingly. – Drew Aug 17 '16 at 14:25
  • @iiirxs your implementation is acceptable in a sense that you will not have duplicates there as you fear, because of a primary key constraint. On the other hand, if there's a case where 2 or more clients will be allocated the same ID via your max() query (which is totally possible), only one of them will get the expected result, other's will see an error, more specifically your application will receive an error during an INSERT operation - how you handle this type of error is up to you – Wintermute Aug 17 '16 at 14:29
  • Sorry @Drew, I rolled back your edit. What you added was factually correct. However, the OP clearly stated in a comment that he was not looking for a solution, just wanted to understand the implication of the current design. Therefore I restricted my answer to describe that the existing primary key constraint will effectively negate the race condition the OP was worried about. I deliberately did not mention any other solution that could be used in place of the solution described in the question. Pls respect this decision. – Shadow Aug 17 '16 at 14:31