How to handle promo code assignment in the Entity Framework without collisions?

Question

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:

1. Contact A requests code.
2. First available promo code is found, and selected from the DB, Promo code A.
3. Contact B requests code. First available promo code is found and selected from the DB, Promo code A.
4. The change to contact A is saved.
5. The change to contact B is saved.

Now, depending on how the promo code assignment is stored, 1 of 2 things will happen:

1. 2 contacts will have the same promo code assigned.
2. 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!

Answer 1

I would add a column to the promo code table, to be able to mark the code as having been used.

Reading the promo code would be done within a Repeatable read transaction, which also marked the promo code as having been used.

This guarantees that the promo code will only be available once.

Answer 2

Couldn't I just do something like this:

UPDATE PromoCodes
SET Assigned = 1
ContactID = x
LIMIT 1

That way, I don't need to read the code beforehand, just assign it over, and then after I can get it:

SELECT * FROM PromoCodes
Where ContactID = x

Any comments on this method?