4

I have been struggling trying to find a way to properly and reliably maintain a balance for accounts in an order/payment system.

Currently, the process consists of three tables. Below example is very simplified but gets the point across.

Table one contains the account ID, name, total paid, total orders, balance etc.

Table two contains payments, date, the account ID (FK) and amount.

Table three contains orders, date, the account ID (FK) and amount.

Calculating the balance is of course as easy as

SELECT SUM(payments.AMOUNT) FROM payments WHERE ID = [account]
SELECT SUM(orders.AMOUNT) FROM orders WHERE ID = [account]

Followed by some simple addition/subtraction math:

UPDATE accounts SET BALANCE = [balance] WHERE ID = [account]

All good - the problem is - how do I maintain this over let's assume years of activity and many, many transactions?

Currently the accounts table is updated every single time a transaction is made, meaning the above three statements run every time. There is of course an index on the ID column in all three tables, and everything is fast and neat for now, but I work with less than 100,000 transactions, meaning everything fits into the RAM and basically all I throw at the database gets executed immediately. Over time this will change, of course - so how do I handle this?

Is it better to mark the accounts as "dirty" and process balancing in a batch job later, or can I keep updating the balance real-time even when I reach the point where the orders and payments tables will not fit into the RAM?

Do I need to segment the orders and payments and "lock" everything before a given time, such as before last calculation? (I would rather not). I ask because then I could do a SUM WHERE DATE > [last lock timestamp] and update with UPDATE BALANCE = [balance] + [value for last lock] and prevent the problem from outgrowing resources.

I don't need a running balance for each transaction. That's all I can find results for when I try to research this problem. I just care about the total amount paid, the total amount ordered and the balance of accounts.

Why don't I calculate on the fly when I need the balance, you ask? Because one must be able to query the system and ask "Which of my (let's say) 50.000 accounts have a balance below 0?" - something we cannot do easily if we do not store the balance with every account.

  • Have you considered triggers? – Vérace May 20 '15 at 20:51
  • I have but I don't see how they change anything. They would need to access the same data. – nickdnk May 20 '15 at 20:52
  • But they are perfect for keeping running totals. – Vérace May 20 '15 at 20:57
  • 1
    Do elaborate. I don't see how they solve the problem of having to fetch thousands of rows spread over years of activity. I did have this on trigger but when I moved to AWS RDS triggers were disabled because of replication incompatibility. I rewrote to regular statements that performed the same operations as the triggers used to. – nickdnk May 20 '15 at 20:59
  • why don't you handle this from application layer? I mean update the balance every time an order is placed or a payment is done? this way you won't face replication incompatibility and if you worried about the performance you can load the balance into your application and update it within your application and at the end of the day update your database (if you are sure balance will be safe while in memory) – Masoud May 21 '15 at 6:48
4

You should think about it a bit differently probably: every single transaction modifies the balance by some value - it either adds or subtracts depending on the type. And you can compute new balance from current balance and the value and type of new transaction. You usually don't need to recompute everything, because it has been done before. You can use those data to check that there was no error with your computation when something goes wrong.

For keeping the balance right when you might process multiple transactions on the same account at the same time, you should read something about locking. I suggest "pessimistic locking" for cases when working with money or similar stuff, you lock the balance of given user when you read it and then compute and update - and you can be sure that any other thread trying to modify the same account will have to wait until this one is done, so it starts with up-to-date data. This will need transactions and InnoDB or some other transactional engine with row locks, MyISAM won't work this way (only possibe to lock entire table which is generally much worse for performance).

As a note, if you have index on (ID, AMOUNT) in payments and orders, then this index can be used to compute your sums really fast for much bigger number of transactions (supposing those tables have many columns - datetimes, some other references, notes...) because it will be much smaller and organized the right way (it's called "clustering") so always only continuous segment has to be read for single ID.

  • Wow. I never even thought of that - and I didn't know about the SELECT FOR UPDATE (which I assume is the one I should use for your suggestion in my transaction). I guess I considered this method to be too unreliable for some reason in my head - but why would it be. – nickdnk May 21 '15 at 7:01
  • Also - can't I just use "UPDATE accounts SET BALANCE = BALANCE + [change] WHERE ID = [account]" instead of the whole lock-select business? – nickdnk May 21 '15 at 7:18
  • You can directly update, that will make the locking. But you usually want to check the value of balance to know if there is enough, at least in case of payment, and for that you have to be sure no-one can check it while you are deciding (otherwise it could make two transactions which each one is under limit but together they are over) - possible with update and then subtracting, but locking select seems more self-explaining to me. – jkavalik May 21 '15 at 7:26
  • There is no limit to how much you can go under current balance, so that doesn't matter. It's an open-bill credit system, designed to be in the negative a lot of the time. – nickdnk May 21 '15 at 7:29
  • Then do the update first, insert new transaction to proper table and commit. If something is wrong, you get rollback and nothing changed. And if everything is OK, balance change is applied and transaction exists so numbers match. If there is no other checking, you probably really need no locking read. – jkavalik May 21 '15 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.