5 ms·
You can use some sort of concurrency index on the ledger. For example you read the ledger entries for Account Id X Those ledger entries are numbered sequentia
by stetrain 3y ago
You can use some sort of concurrency index on the ledger.
For example you read the ledger entries for Account Id X
Those ledger entries are numbered sequentially up to the current index Y
You calculate the current balance, approve the withdrawal, and try to insert the next ledger entry as Y + 1
If another operation has already inserted a Y + 1 your insert fails and you throw an error or try again, starting with the new ledger state and balance.
Basically optimistic concurrency but knowing that you are always appending to the ledger and that the index is always incrementing.
- amw-zero 3y ago> Also, consider storing the transactions that change the balances (credits, debits, etc) in a ledger and calculate the balance. You can avoid the complex update logic and keep your accountants and auditors happy. This was written (to me) as if using a ledger alone would get rid of the race conditions caused by the read committed isolation level. But what you described is manually implementing locking, which requires additional schema and application logic.
- stetrain 3y agoOptimistic concurrency and a ledger lets you do those checks without locking any rows and you don’t have multiple processes fighting to update a single row. Instead of leaving an AccountBalance row locked for the entirety of the Withdrawal process, you have a condition on the insert and a retry mechanism if that insert fails. You still have to implement concurrency logic, it’s just less likely to have the database isolation be your bottleneck.