Multiple processors trying to update the same row in database

Viewed 45

I have an Azure function app that triggers when data is enqueued to a service bus queue. In the Azure function, I implemented a charging method. First I get the particular row from the database and check the account points.

SELECT * FROM UsersAccountPoints WHERE UserId = @UserId

Then I update the points.

UPDATE UsersAccountPoints
SET FundsChange = @ChargeAmount, FundsAmount -= @ChargeAmount
WHERE UserId = @UserId

When I run this on local, it runs perfectly fine. But when I deploy it on Azure, the function app starts scaling. Then parallel processors start updating the same row of the UsersAccountPoints table, giving me confusing results.

Then I tried UPDLOCK.

SELECT *
FROM UsersAccountPoints WITH (UPDLOCK)
WHERE UserId = @UserId

But this also gives me the same result. I use the Azure premium function, SQL Server database, and Dapper for ORM. Is there a better way to handle this kind of scenario than UPDLOCK?

0 Answers
Related