CODEWITHSUNDEEP
sql-serverselectatomic
Traderhut Games
Traderhut Games

Reputation: 1292

Need to update and select in one atomic operation

I am writing code to auto-correct faults in payment systems - before they request a payment, they update [Table] to have all the data that is needed to process the payment. If it works, the Status=1, if it fails, Status=0.

Later, if a request succeeds, then it will reprocess any rows that have a status=0. (only 1 gets reprocessed per successful request to avoid overloading the payment system once it comes back online, but multiple requests could come into the SQL Server for failed records at the same time)

I have a table that I was told to do something like:

DECLARE @id int;
SET @id = (select top(1) id from [table] where Status = 0 order by id );

select * from [table] where id = @id;
update [table] set Success = NULL where id = @id;

But if queries ran at the same time, they could get the same value for @id, and both of them reprocess the payment - creating duplicates.

I'm thinking doing this:

DECLARE @id int;
DECLARE @tmp int;

SET @id = (select top(1) id from [table] where Success = 0 and RetryAttempCount < 5 order by id);

-- Code to handle multiple people hitting this at once.  One will get -1, and the other -2
-- in the @tmp var.

update [table] set Success = Success -1, @tmp= Success -1 where id = @id;   

if (@tmp = -1)  -- then we got it
   BEGIN
   update [table] set RetryAttemptCount = RetryAttemptCount +1, RetryDate = GetDate(), Success = NULL
       Where id=@id
   select * from [table] where id = @id;
   END

END

My question: Is there a better way? This seems to work, returning either a row that needs reprocessed, or no result set.

Upvotes: 0

Views: 337

Answers (1)

Dark Falcon
Dark Falcon

Reputation: 44181

If the order in which operations are processed doesn't matter, why not just do this?

DECLARE @id int;
UPDATE TOP(1) [table] SET @id=id, Success=NULL WHERE Success=0 AND RetryAttemptCount<5;

Upvotes: 1

Related Questions