T-SQL transactions and table locking

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 se开发者_运维问答lected, 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?

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;

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;

Use:

SELECT * 
  FROM [dbo].[MyTable] (UPDLOCK)
 WHERE [flag] IS NULL;

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

For more info on locking hints:

• The SELECT/UPDATE problem, or, why UPDLOCK?
• Locking Hints

You can wrap these two statements in a transaction with read_committed or more restricted scope. Its a bit expensive and might cause other issues. King's solution is more workable.