Modelling a Bank Account - Handling concurrent withdrawls
Posted: Sat May 02, 2009 3:20 am
Hi Guys,
I'd like to start off a discussion regarding modelling a bank account in PHP, more specifically making sure the current balance of the account is always greater than 0 (no overdraft on this account just simple deposits & withdrawls).
Lets start with the following code sample
In the example above the data will be saved to a MySQL database with InnoDB table types. The structure will be similar to below
account
id
user_id
created date
transactions
id
type - deposit / withdraw
amount
date
account_id
Very simple structure. You may notice that the account does not have a current balance field. Im thinking this should be calculated as needed. Also the type field in the transactions table is not used for working out the current balance - all withdraws will be stored with a negative amount and all deposits with a positive amount so the following SQL
SELECT SUM(amount) FROM transactions WHERE account_id = 1
Should give you the current balance for account 1.
Now the explanation is out of the way let me move on to my problem. Lets say we have 3 users. User A has a bank account with a balance of 100. User A owes user B 75, and User A also owes user C 50. Not enough in the account to cover both transactions so one should not succeed.
With the code snippet above if user b / c try to withdraw money one after another the second transaction will rightly fail at the following line
as the account will no longer have sufficient funds.
However if the transactions are taken place at exactly the same time we may end up with a race condition with user C checking the balance just before user B has a chance to withdraw the money, and the account will have a negative balance. See below
I'm guessing the solution to this will be some kind of locking mechanism either on the database or elsewhere however this is probably the first time i've run in to this kind of problem.
Does anyone have any other suggestions?
I'd like to start off a discussion regarding modelling a bank account in PHP, more specifically making sure the current balance of the account is always greater than 0 (no overdraft on this account just simple deposits & withdrawls).
Lets start with the following code sample
Code: Select all
$account = new Account();
$amount = 20;
if ($account->hasSufficientFunds($amount)) {
$account->withdraw($amount);
}
$account->save();
account
id
user_id
created date
transactions
id
type - deposit / withdraw
amount
date
account_id
Very simple structure. You may notice that the account does not have a current balance field. Im thinking this should be calculated as needed. Also the type field in the transactions table is not used for working out the current balance - all withdraws will be stored with a negative amount and all deposits with a positive amount so the following SQL
SELECT SUM(amount) FROM transactions WHERE account_id = 1
Should give you the current balance for account 1.
Now the explanation is out of the way let me move on to my problem. Lets say we have 3 users. User A has a bank account with a balance of 100. User A owes user B 75, and User A also owes user C 50. Not enough in the account to cover both transactions so one should not succeed.
With the code snippet above if user b / c try to withdraw money one after another the second transaction will rightly fail at the following line
Code: Select all
if ($account->hasSufficientFunds($amount)) {
However if the transactions are taken place at exactly the same time we may end up with a race condition with user C checking the balance just before user B has a chance to withdraw the money, and the account will have a negative balance. See below
Code: Select all
$account = new Account();
$amount = 20;
// user c is checking the balance here just after user b, but before the withdraw is made
// therefore the account appears to have sufficient funds for the second transaction
if ($account->hasSufficientFunds($amount)) {
// by the time user c gets here the account no longer has sufficient funds to cover the
// transaction - however it will still go through which is bad.
$account->withdraw($amount);
}
$account->save();
Does anyone have any other suggestions?