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

Assuming that [flag] is a bit field. I would:

Update [myTable] set flag = 0 where flag is null;
select * from [mytable] where flag = 0;
Update [mytable] set flag = 1 where flag = 0;

Answer 2

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.

Answer 3

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

Answer 4

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;

Answer 5

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;