Read write problem on an append only table

Question

Suppose I have this append-only SQL table representing deposits/withdraws from/to accounts. The 3 rows below will render the balance of account A to be -$10

ID	Account	Credit
1	A	$100
2	A	-$50
3	A	-$60

If the inserts are concurrent, how can I implement validation that prevents the total balance of accounts from becoming negative?

I understand that the validation depends on the aggregated values of the existing entries, and since none of the existing entries will be updated, the traditional transaction protection provided by the isolation levels no longer works. This seems to be a generic problem that affects all append-only tables. But, I could not find any discussions about the problem.

My questions are

1. Is this a real problem?
2. If it is, what are the generic solutions to this kind of problem.

Answer 1

Yes, the problem is real. One pattern that works:

1. Have one table where the current balance is kept. This will be the source of truth for you. One row per account.

2. The transaction table (the append-only) should act as a "history" table.

3. When a new transaction (credit/debit) request comes in:

   a. Acquire a lock on the account balance row (1st table)

   b. Run the validation: balance + request.amount >= 0, if it fails fallback, else make entry into history (2nd table) table - also update the new balance into 1st table.

   c. release the lock

Why have a table with the current balance?

1. Balance will always be required in your application. All your business logic depends on it. Instead of running a summary over the history table, having a dedicated place for it is far more optimal.

2. To handle concurrency, you need to put a guard somewhere --- preferably one place where all threads have to pass through :)

hope this helps