CODEWITHSUNDEEP
sqlsql-servert-sqltransactionslocking
heath
heath

Reputation: 1109

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 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?

Upvotes: 2

Views: 6233

Answers (4)

gbn
gbn

Reputation: 432662

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;

Upvotes: 6

Remus Rusanu
Remus Rusanu

Reputation: 294457

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;

Upvotes: 6

OMG Ponies
OMG Ponies

Reputation: 332771

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:

Upvotes: 3

GrayWizardx
GrayWizardx

Reputation: 21241

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.

Upvotes: 2

Related Questions