How can you store ledger operations without locking on the balance? The race condition still exists - you can't accept a withdrawal if the user doesn't have enough funds.
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.
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.
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.
You can.
ATMs around the country aren't doing account-level locking. If you overdraw, you overdraw.
If you store the ledger operations (reliably! no half-writing them and then throwing an exception!), then your system knows what happened.
Having a ledger which you're confident about is more important than trying to use exceptions to prevent 'bad' ledger messages being written.
I don’t think you want to let a $500k withdrawal go through optimistically.