SQL SUM expression and Lock

I would consider EITHER:

Storing a balance on the account record, along with the date for which the balance is accurate.

Getting the current balance is a matter of reading the account balance, and then including any transactions since that date.

You can have a scheduled job that recalculates and timestamps that balance at an hour past midnight.

OR (and this is my preferred solution):

Every time a transaction or batch of transactions is loaded, lock the relevant account records and update them with the values from the insert as part of the same transaction.

This has the advantage of serialising access to the account, which can then help with determining whether a transaction can go ahead or not because of decisions based on the balance calculation.

If you want to avoid having the balance on the user account, something that could have a better performance, the approach I would experiment would be:

• Each transaction would be related to only one account.
• Each transaction would have the account balance after that transaction.

Therefore, the last transaction for that account would have the current balance.

Ex.:

TransactionId | AccountId | Datetime | Ammount | Balance             1 |         1 | 7/11/16  |       0 |       0             2 |         1 | 7/11/16  |     500 |     500             3 |         1 | 7/11/16  |     -20 |     480             4 |         1 | 8/11/16  |      50 |     530             5 |         1 | 8/11/16  |    -200 |     330 

This way you would be able to get the account balance (last transaction with that accountId) and you would be able to provide a better view into the balance change over time.