Best approach when speaking about concurrency PHP

user_13683001 0
Viewed 100

I'm working on php + mysql project where we use a balance column that is used to keep track of a user's balance. Whenever a user makes a purchase their balance gets deducted. However I am trying to figure out what the best way is to tackle multiple requests from the same user when trying to purchase a product. I've done some research and wrote the following 2 approaches, I don't quite see a difference here could anyone explain what the difference between those 2 approaches is and which one is best regarding concurrency?

Approach 1:
START transaction
"SELECT balance from users where user_id = 1 FOR UPDATE"

check if balance - product price is enough then UPDATE
commit

Approach 2:
"UPDATE users set balance = balance - 30 where user_id = 1 AND balance - 30 >= 0"

As you can see option 2 is way less code, but I still see a lot of people recommending the first approach (locking the row first and then update).

Could anyone help me understand what actually the difference here is and which one is best to use when you care about concurrency and want to avoid multiple requests possibly making the balance column invalid? If you have better approaches please let me know, maybe I am overthinking the situation. I am using PDO.

where-clause sql-update mysql sql php
2 Answers

Either solution definitively will prevent race conditions. SQL databases have atomicity, consistency, isolation, and durability (ACID) properties. The cool thing about SQL databases: you don't have to worry about race conditions if you get the transactions right.

Single statement operations like the UPDATE in your second example are always atomic transactions. And, the explicit transaction in your (correctly written) first example is also an atomic transaction.

Like @GMB, I prefer to use single statement operations wherever possible. But that's just because the code is easier for the next programmer (or my future self) to understand. Either approach works.

And, if your business rules get more complex, you'll probably need multistatement transactions. That's an advantage of your first approach.

Both your solutions are ACID-preserving.

user_2056080

The second approach is the right way to to. That's a single query, that at the same time checks if the user has sufficient founds, and updates their balance.

The database will properly handle concurrency for this query under the hood, as opposed to the first approach, that requires using a transaction to avoid race conditions. The row is locked during the update: if several sessions try to update the same row, the updates are execute sequentially, and the changes performed by each query affect the following query.

From your application, you would typically fire the query, and then check whether a row was affected. If yes, then you know the update was executed (so the user had sufficient founds) - else, the transaction was rejected (either because the user does not exists, or because it has insufficient founds).

user_106767160
Related