Let's say I have a table of promo codes, and a table of contacts. Each contact can have 1 promo code, and each promo code can only be assigned to 1 contact. How would I employ an assignment program that would select a promo code from the table, and assign it to a contact, without having concurrency issues/collisions.
Worst case scenario:
- Contact A requests code.
- First available promo code is found, and selected from the DB, Promo code A.
- Contact B requests code. First available promo code is found and selected from the DB, Promo code A.
- The change to contact A is saved.
- The change to contact B is saved.
Now, depending on how the promo code assignment is stored, 1 of 2 things will happen:
- 2 contacts will have the same promo code assigned.
- Contact B will receive an error, in the case that a duplicate key exception is thrown.
FYI, I am using a DataContext that is being held in the HTTPContext so that it is available per HTTP request. Not sure if that matters.
So, using C# and the Entity Framework, how can I accomplish this promo code assignment without encountering these types of collisions? Thanks in advance!