Record Transactions, not Balances

The typical example of an atomic transaction is a financial transfer. You want to make sure that you reduce the balance of one account by $100 and increase the balance of another account by $100 atomically. It is incorrect for one to occur without the other. And so, the example shows us, you wrap these two actions within one atomic transaction.

The problem is, this example is completely bogus. Banks don't work this way. They record individual transactions, not account balances.

I learned this lesson the hard way when I worked on the eCard product at Radiant Systems -- a refillable gift-card service. Storing account balances is a Bad Idea. Here's what can go wrong.

Select/Update
In order to adjust an account balance stored directly in the database, you have to issue two SQL statements: SELECT the current balance, perform the calculation, then UPDATE to the new balance. (Sure, you could do this with the one statement "UPDATE SET balance=balance-100", but then you couldn't check for overdrafts.) Even within a database transaction, there is the possibility that the record is changed between the SELECT and the UPDATE. This could happen any time the transaction isolation level is set to something lower than the most paranoid "serializable". To fix this, raise the level, or apply a query hint to the SELECT statement, such as "for update".

Whichever solution you choose, you have just increased the locking in your database, and decreased concurrency and scalability. And we have seen that occasionally, even these tight restrictions don't work as advertised. There are still some incorrect balances that we can't explain to this day.

Reconciliation
When you have a distributed system, such as Radiant's eCard in caching mode, you always have to bring the transactions together at some point and verify correct balances. This is the same process that you go through each month when you balance your check book, only at a larger scale and with greater automation. Reconciling balances is simply impossible. You need to identify and compare individual transactions (think check numbers), and add missing transactions to each list.

Diagnostics
When things go wrong, you often need to go back through the transaction history to see where it diverges. If you don't have this history, then you obviously have a problem. But even if you just keep a log in parallel with a balance (as we did at Radiant), you will have a hard time determining exactly which of those transactions was applied incorrectly.

The best practice is to keep a running list of transactions by account. Occasionally, you can snapshot and archive those transactions, like an accountant closing the books each quarter. Then to find the current balance of an account, apply all recent transactions to the snapshot. This solution is scalable, distributable, and auditable.

Leave a Reply

You must be logged in to post a comment.