I am building a banking system in NodeJS and an SQL database. The two main functionalities I need are deposit and withdraw. I want to make sure that there are no race conditions.
Ex: User has 100$ balance and withdraws 100$ twice quickly and ends up with -100$.
Can the following query have race conditions?
update account set balance = balance - x where balance > x
If this does not solve the problem:
If I use PostgreSQL, will a check constraint on my balance column suffice to eliminate all race conditions?
If I use MySQL, what are my options?
I am curious on what are the different ways to achieve this in both PostgreSQL and MySQL
So, you can do the following:
withdraw() function, i.e. start a new transaction.SELECT .. FOR UPDATE to read the recordUPDATE the valueNow, when user tries to withdraw twice quickly, one of the call will start the transaction and issue SELECT.. FOR UPDATE call, that will block the other thread from reading the data. It will be only unblocked once first thread will finishes, i.e. it will prevent race condition.
Here's MySQL's documentation about it.