In a Core Banking Application we have a table called transactions, which includes columns amount and balance. In a high-concurrency data insertion environment, I want to update the balance column by adding the following value: current amount + previous record's balance.
Example:
table: transactions
| id | amount | balance |
|---|---|---|
| 1 | 100.00 | 100.00 |
| 2 | 50.00 | 150.00 |
| 3 | 30.00 | 180.00 |
We attempted to use the trigger below to achieve this goal. However, when running with multiple processes creating new transactions, the results become inconsistent.
CREATE OR REPLACE FUNCTION update_balance() RETURNS TRIGGER AS $$
BEGIN
UPDATE transactions
SET balance = NEW.amount + COALESCE((SELECT balance FROM transactions WHERE id = NEW.id - 1), 0)
WHERE id = NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_balance_trigger
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION update_balance();
We are considering queuing an update process but are concerned that this won't resolve consistency issues and may degrade performance.
How can we maintain both consistency and performance?
Oracle has a feature where you can lock rows that you plan to update so that data integrity is persisted. Reading up I saw PostgreSQL has a similar feature and a similar syntax.
From the description, the mechanism seems to be identical and from the experience I had in Oracle, we never had inconsistencies when using it, so I expect you won't have any in PostgreSQL either. Because you're working on balances, you'd have to wait for changes, otherwise, using NOWAIT would return an error and you might "lose" that balance update request.
Regarding your other approach, we have the user operation table (where just about any click in the app does "something") which uses the queuing approach. Performance wise, I wouldn't say it'd have an overhead (other than the usual in these situations), although, profiling the usage is always the best approach rather than assuming, however, since you'd queue the balance update and if there are a lot of transactions for your client, then they'd see their real bank account balance value later instead of real-time. How much "later"? It depends (again, you'll need to do some performance tests to have a proper evaluation).
In the meantime, try locking rows with SELECT 1 FROM transactions WHERE id = X FOR UPDATE. Obviously, remember to disable the trigger.
You could try a NOWAIT lock and only queue the "lost" updates, since normally you'd update each account separately, so it would make sense to have exclusive locks for each account that needs and update and queue that account line if it needs more subsequent updates for multiple updates. Again, you'd need to test to see if combining them is a proper approach for this use-case.