Page 1 of 1

Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 02, 2009 3:20 am
by deadoralive
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

Code: Select all

 
$account = new Account();
$amount = 20;
 
if ($account->hasSufficientFunds($amount)) {
    $account->withdraw($amount);
}
 
$account->save();
 
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

Code: Select all

 
if ($account->hasSufficientFunds($amount)) {
 
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

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();
 
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?

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 02, 2009 3:43 am
by Benjamin
If you wrap the queries that check the balance and insert the transaction into transactions, you won't have an issue with race conditions. Have a look at: http://www.databasejournal.com/features ... -MySQL.htm

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 02, 2009 9:22 am
by deadoralive
fantastic just what i wanted!

cheers

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 09, 2009 9:01 am
by kaisellgren
That kind of approach is vulnerable to race condition attacks. You could just lock the table/row (depending on the engine) to make sure there will be only one thread processing the value at a time.

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 09, 2009 9:54 am
by Benjamin
How are atomic transactions vulnerable to race condition attacks?

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 09, 2009 9:59 am
by kaisellgren
Without proper race condition handling, nasty replay attacks and all sorts of tracking avoidance can happen. My last post was a response to the first post, just to clarify.

Re: Modelling a Bank Account - Handling concurrent withdrawls

Posted: Sat May 16, 2009 3:20 am
by deadoralive
Cheers guys, much appreciated