Comment by agentultra | Hacker News Reader

Comment by agentultra | Hacker News Reader

Generally, for a lot of application usage patterns, read committed is the default for a reason.

From the Postgres docs:

> The partial transaction isolation provided by Read Committed level is adequate for many applications, and this level is fast and simple to use. However, for applications that do complex queries and updates, it may be necessary to guarantee a more rigorously consistent view of the database than the Read Committed level provides.

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.

Stricter isolation levels are useful but have a lot of trade-offs to be aware of.

Sometimes restructuring your data is a lot more effective than trying to reason about locks and concurrency. The more locks you have to more points of contention, dead locks, etc you have to be concerned about. Humans and general rules/folklore are alright at avoiding some mistakes but these systems get complicated quickly and can hide a surprising number of errors.

As for testing these things, I've gotten a lot of good mileage out of model checking. TLA+ and Alloy are better at finding concurrency errors than I am at avoiding them.

Can you recommended a good article that goes over the ledger pattern (the easy part) and the performant calculation of balances as the ledger grows? I'm going to start working on a warehouse management system in the coming months and that would come in handy.

I don't have a link but when I've implemented systems with a "ledger" of changes I would have a field that indicated whether the ledger item had been "posted" to the the affected account(s) and therefore the balance was just the account balance +/- any unposted ledger items.

I've had ledgers that were posted once a day, or more often, up to nearly real-time.

But once a ledger item is posted, it should never be changed. If an error is discovered, make a new ledger entry to correct it.

The pattern in general is called "event sourcing", but unfortunately it's popular enough to make a lot of bad articles appear, and I can't recommend a good one.

The ledger pattern is called double-entry accounting.

Make sure your product managers understood that you’re being tasked with creating an accounting system. They probably haven’t really thought it through very well.

Relevant, not all logs are ledgers. https://twitter.com/sbellware/status/1742653635387941099

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.

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.

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

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.

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

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.

Reply on news.ycombinator.com