How to lock row for update and prevent Race Conditions ?

Question

Hi,

I have an API that runs on 2 load balanced servers and they are connected to one DB server. The API is deducting from the user balance with each request and I need to know if the sql server engine will manage the lock on the row by default and prevent Race Conditions or there is something I need to do to implement this lock ?

In case the TPS on the API is very high, I need to prevent the 2 servers from (Race Conditions) deducting from the same user at same moment and cause incorrect deduction on the ROW level not table. For example, if the user balance is 10 and server A will deduct 1 and at same time server B got another request and is going to deduct also 1 the user balance should be 8 not 9.

How can I achieve this on Table Row level ?

Answer 1

Since I don't see the code or the business logic I will have to speak from generic terms.

Yes, you will have to add something to avoid accidents. The standard solution would be to start a transaction and read the current balance using the UPDLOCK hint. A UPDLOCK is a shared lock which does not stop readers. However, only one process at a time can have a UPDLOCK, so if two processes are coming in at the same time, the second one will be blocked at this point until the other has committed and completed its update.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hi Ahmed,

Single-threading may be what you want in this case.

http://programmers.stackexchange.com/questions/263726/how-to-prevent-race-conditions-in-a-web-application

https://coderanch.com/t/474917/JDBC/databases/prevent-race-condition-reading-updating

---

Please click Mark As Answer if my post helped.

Answer 3

I have used WITH (UPDLOCK, ROWLOCK) and it works on row level but for both update and select. Is there a way to make it works only on update without blocking select ?

Answer 4

I have used WITH (UPDLOCK, ROWLOCK) and it works on row level but for both*update* and *select.* Is there a way to make it works only on*update* without blocking *select* ?

At this stage I don't want to make any comment without seeing the code, so that I know what I'm talking about.

Answer 5

Hey,

I'm facing a problem for which i'm not able to find an exact solution. 
My scenario is that the application is going to run in a multi-user environment and If someone opens the form the ID is get by the query

Select top 1 ID from myTable order by ID DESC

Now if one user opens the form and gets the ID 53 another opens and gets the ID 53 because 53 isn't commited by the first user and in this case my insert query fails.

I'm not able to find the exact solution. 
Any help will be greatly appreciated.

Best Regards,

Muhammad Asad

 

---

Asad