4

Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / concurrency issues?

Option 1)

First query the table and see if the relationship already exists, if it does just notify the user that made the request.

Option 2)

While inserting, use the WHERE EXISTS syntax (2nd example).

Option 3)

Use Postgres 9.5 Beta for the UPSERT functionality. (I suppose using a Beta version for a school project isn't that bad of an idea?).

What option would be best when it comes to concurrency. I'm not very experienced with SQL (just the standard stuff for CRUD applications). But as far as I know, in the first option, a relationship could be inserted by another user right after querying the junction table but before actually inserting a relation myself. I suppose there won't be any duplicates because I've got a composite primary key in that junction table, but I feel like it's bad practice to just let it make the exception and continue. Furthermore, in my current code, I'd return a 500 http status in this case (because of a db exception), which doesn't really seem right.

I've read I could partially solve the above by 'locking' the database/table, but I don't know if that's the right method.

Finally, I have no idea if there's a race condition when it comes to the 2nd option.

  • Not sure I understand the difference between your listed Option 2 and Option 3 .... typically one does a WHERE EXISTS in inserting if they're doing an UPSERT operation, but an actual UPSERT operation is not available. (UPSERT = IF EXISTS, UPDATE, ELSE INSERT) – Joishi Bodio Oct 29 '15 at 19:05
  • Also .. what are you trying to achieve from a concurrency standpoint?.. Do you have 3 threads running each doing a single INSERT? Are they sharing the same database connection, or do they have their own?.. – Joishi Bodio Oct 29 '15 at 19:07
  • If I wanted to add a user to a team .. This assumes an existing user and only INSERT INTO teams_users ... is your topic, right? – Erwin Brandstetter Oct 30 '15 at 1:59
  • @JoishiBodio Basically what I mean is, say there's about 3 people on a team authorized to add an existing user to a team. They execute a PUT request to e.g. /teams/1/users/2 (adding user with ID 2 to team with ID 1 in junction table teams_users). If that specific relation is already in the table, it'll throw a duplicate key exception. My question is, how do I optimally deal with that? I'm afraid that when using option 1 or 2, another user that's authorized to add a user to a team will magically at the exact same time add that specific user as well, resulting in a race condition. – Pieter-Jan Vandenbussche Oct 30 '15 at 23:43
  • @ErwinBrandstetter Yep, that's what I mean, sorry if I didn't clarify it correctly. Oh and btw, regarding your other comment, I'm a student, not a teach or anything :) – Pieter-Jan Vandenbussche Oct 30 '15 at 23:43
2

If I wanted to add a user to a team, what's the best option

For just INSERT INTO teams_users ... and without raising an exception, the answer is option 3: Use Postgres 9.5 Beta for the UPSERT functionality. For a "school project" this is the best option anyway. Your students will want to study the latest version.

INSERT INTO teams_users (team_ID, user_ID)
VALUES (1, 2)
ON CONFLICT ON CONSTRAINT teams_users_pkey DO NOTHING;

Where teams_users_pkey is the actual name of your PK constraint.

This inserts the new row or does nothing if the unique index would raise a duplicate key violation. Designed to be fast and safe against concurrent writes.

Additional question

Finally, I have no idea if there's a race condition when it comes to the 2nd option.

Option 2 being:

While inserting, use the WHERE EXISTS syntax (2nd example).

That would actually be WHERE NOT EXISTS in your case and yes, there is a race condition. Two parallel transactions could find that a certain combination does not exist yet at virtually the same time and happily try to insert it. The slower one would run into a unique violation and error out. Not much of a problem if your application is prepared for that eventuality.

  • Oh, so it's not bad practice to just let it error out? How would I then give a response to the client that the relation is already there? Check if the exception that was thrown is a duplicate key exception and assume it's because I tried to insert a relation which is already there? – Pieter-Jan Vandenbussche Oct 30 '15 at 23:45
  • @Pieter-JanVandenbussche: Yes, you'd get 23505 unique_violation. See error codes. But it should only happen on extremely rare occasions under heavy concurrent write load. – Erwin Brandstetter Oct 31 '15 at 1:46
  • Alright, I'll implement that. Thanks for the helpful answers! – Pieter-Jan Vandenbussche Oct 31 '15 at 15:55
  • Exception handling at the DB level is a thing, for sure. Often times the best solution is to let the attempt it, catch the exception, and send back a message accordingly. – Joishi Bodio Nov 1 '15 at 0:06
1

If you are concerned about data integrity (making sure that all the appropriate table are populated BEFORE someone gets a chance to read the data), then you need to make sure that all three inserts are done within the same transaction block.

Begin transaction
Insert 1) insert into users
Insert 2) insert into teams
Insert 3) insert into teams_users
Commit transaction

If you do not perform these within the same transaction block, then there's the possibility that someone will select from users before teams and teams_users have been inserted into.

  • This would not solve the original issue of concurrent inserts into the teams_users table causing an exception, bc with read committed isolation level the race condition still exists. – BateTech Oct 30 '15 at 10:49
  • Yes, but I'm not exactly sure what he's trying to do - he needs to better explain in (I asked him clarifying questions in comments to his OP that have not been answered yet) – Joishi Bodio Oct 30 '15 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.