T-SQL transactions and table locking

Question

If I want to select all records in a table that have not been processed yet and then update those records to reflect that they have been processed, I would do the following:

 SELECT * FROM [dbo].[MyTable] WHERE [flag] IS NULL;
 UPDATE [dbo].[MyTable] SET [flag] = 1 WHERE [flag] IS NULL;

How do I ensure that the UPDATE works on only the records I just selected, ie, prevent the UPDATE of any records that may have been added with [flag] = NULL that occurred AFTER my SELECT but before my UPDATE by another process? Can I wrap those two statements in a transaction? Do I have to put a lock on the table?

Answer 1

Single call, no transaction needed by using the OUTPUT clause.

XLOCK exclusively locks the rows to stop concurrent reads (eg another process looking for NULL rows)

UPDATE dbo.MyTable WITH (XLOCK)
SET flag = 1 
OUTPUT INSERTED.*
WHERE flag IS NULL;

Answer 2

Use the OUTPUT clause to return a result set from the UPDATE itself:

UPDATE [dbo].[MyTable] 
SET [flag] = 1 
OUTPUT INSERTED.*
WHERE [flag] IS NULL;