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.

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.

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.

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.

Say I've got a 'users' table, a 'teams' table 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?).

Now I'm wondering what option would be best when it comes to concurrency. I'm not very experienced with SQL at all (besides 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 yourself. Now 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 on (furthermore, in my code, the way it is right now, 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.

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.