MS SQL locking for update

Question

I am implementing a competition where there might be a lot of simultaneous entries. I am collecting some user data, which I am putting in one table called entries. I have another table of pre-generated unique discount codes in a table called discountCodes. I am then assigning one to each entry. I thought I would do this by putting a entry id in the discountCodes table.

As there may be a lot of concurrent users I think I should select the first unassigned row and then assign the entry id to that row. I need to make sure between picking an unassigned row and adding the entry id that another thread doesn't find the same row.

What is the best way of ensuring that the row doesn't get assigned twice?

Answer 1

Something can be done like

The following example sets the TRANSACTION ISOLATION LEVEL for the session. For each Transact-SQL statement that follows, SQL Server holds all of the shared locks until the end of the transaction. Source:MSDN

USE databaseName;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
GO
SELECT * 
    FROM Table1;
GO
SELECT * 
    FROM Table2;
GO
COMMIT TRANSACTION;
GO

Read more SET TRANSACTION ISOLATION LEVEL