3 ms·

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 eno

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.

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 ago
    Optimistic 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.
mrkeen 3y ago
> you can't accept a withdrawal if the user doesn't have enough funds. 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.
- amw-zero 3y ago
  Maybe an ATM allows that, because the max you can take out of an ATM is $500 or so. I don’t think you want to let a $500k withdrawal go through optimistically.

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.

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 ago
  Optimistic 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.

mrkeen 3y ago

> you can't accept a withdrawal if the user doesn't have enough funds. 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.

amw-zero 3y ago
Maybe an ATM allows that, because the max you can take out of an ATM is $500 or so. I don’t think you want to let a $500k withdrawal go through optimistically.