Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Testing Postgres race conditions with synchronization barriers

lirbank.com

Post reply on HN
Print view View original

41–50 of 60 posts

Re: Testing Postgres race conditions with synchronization barriers

#41

Post by mfcl »

piskov wrote: post #20

That whole article should have been: Use transactions table (just a name, like orders) On it have an Insert trigger. It should make a single update with simple “update … set balance += amount where accoundId = id”. This will be atomic thanks to db engine itself. Also add check constraint >= 0 for balance so it would never become negative even if you have thousands of simultaneous payments. If it becomes negative, it…

The point of the article is to explain barriers and demonstrate them.

The logic used for crediting amounts of money is not important.

Re: Testing Postgres race conditions with synchronization barriers

#42

Post by lirbank »

fake-name wrote: post #40

I'm confused. > The simplest case: no transaction, just a SELECT and an UPDATE with a barrier between them: There is no context where you do not have a transaction. Postgres requires them. It's likely that the library the author is using is doing automatic implicit transactions, but it seems like the author needs to understand their tools a bit better.

You're right, Postgres wraps every statement in an implicit transaction. The point of that first example is that the SELECT and UPDATE are in separate auto-committed transactions - there's no explicit transaction block wrapping both. So another connection can change the value between your SELECT and your UPDATE.

Re: Testing Postgres race conditions with synchronization barriers

#44

Post by OrangeDelonge »

scottlamb wrote: post #25
lirbank wrote: post #10

Earlier quoted context omitted.

Interesting! The barrier approach is more targeted: you specify the exact interleaving you want to test rather than exploring all of them. Trade-off is you need to know which interleavings matter, but you get deterministic tests that run against a real database instead of a simulated runtime. Exploring exhaustive interleaving testing against a real Postgres instance could be a fun follow-up - I'd be curious if it's p…

I think you could still do it against a real database—you're already setting it up to a known state before each test, right? Obviously there'd be more runs but I'd expect (hope) that each task would be sufficiently small that the number of permutations would stay within reason. There would be some challenges for sure. Likely optimistic concurrent patterns would require an equivalent of loom's `yield_now` [1] to avoid…

Do you know you’re just talking to an LLM? Everyone else in this post also seem oblivious to it or maybe they just don’t care? Why do I even read comments anymore sigh

Re: Testing Postgres race conditions with synchronization barriers

#45

Post by jwilliams »

This post confuses me a little. With my tests I try not to "reach inside" systems unless it's quite a specific integration test. Especially databases. In this case I feel like we're just... testing known PostgreSQL behavior?

Or to put another way; as others have observed, this could be solved with atomic updates and in some case SERIALIZABLE. These are right tools for balance operations - and if they’re used I’m not sure they need testing in this manner?

Re: Testing Postgres race conditions with synchronization barriers

#46

Post by electronvolt »

Arifcodes wrote: post #39

[dead]

I mean, you say that, but systems like Spanner exist & I think the fact that it's used for Gmail, Docs, etc. has demonstrated that for a large range of OLTP workloads, serializable everywhere and also performant /is/ possible to architect for -- with the right combination of both database + application design.

That isn't to say it's correct everywhere, but I'd maybe be a little more suspicious of "We want OLTP but we can't use serializable" in a vacuum.

Of course--there are obvious cases like "We can't use serializable because of how our database implements it" or even "because we can't be arsed to figure out how to structure all our data accesses to avoid conflicts and aren't paid enough to solve that problem", but I feel like those are a bit more honest than "Because of performance reasons, in all cases". :)

Re: Testing Postgres race conditions with synchronization barriers

#47

Post by electronvolt »

sealeck wrote: post #26
danielheath wrote: post #24

Earlier quoted context omitted.

SERIALIZABLE is really quite hard to retrofit to existing apps; deadlocks, livelocks, and “it’s slow” show up all over the place when you switch it on. Definitely recommend starting new codebases with it enabled everywhere.

Do you have examples of deadlocks/livelocks you've encountered using SERIALIZABLE? My understanding was that the transaction will fail on conflict (and should then be retried by the application - wrapping existing logic in a retry loop can usually be done without _too_ much effort)...

I guess I'd say -- I think you're right that you shouldn't (ideally) be able to trigger true deadlocks/livelocks with just serializable transactions + an OLTP DBMS.

That doesn't mean it won't happen, of course. The people who write databases are just programmers, too. And you can certainly imagine a situation where you get two (or more) "ad-hoc" transactions that can't necessarily progress when serializable but can with read committed (ad-hoc in the sense of the paper here: https://cacm.acm.org/research-highlights/technical-perspecti...).

Re: Testing Postgres race conditions with synchronization barriers

#48

Post by lirbank »

jwilliams wrote: post #45

This post confuses me a little. With my tests I try not to "reach inside" systems unless it's quite a specific integration test. Especially databases. In this case I feel like we're just... testing known PostgreSQL behavior? Or to put another way; as others have observed, this could be solved with atomic updates and in some case SERIALIZABLE. These are right tools for balance operations - and if they’re used I’m not…

Fair concern about reaching inside systems - it's not something to do lightly. The hooks are designed to be minimal: production code never calls them, they only activate in tests. But the core point is narrower than the thread might suggest - the article isn't about whether to use atomic updates vs locks vs SERIALIZABLE. It's about when your code has operations that could race, how do you prove your handling actually works?

Re: Testing Postgres race conditions with synchronization barriers

#50

Post by Arifcodes »

electronvolt wrote: post #46
Arifcodes wrote: post #39

[dead]

I mean, you say that, but systems like Spanner exist & I think the fact that it's used for Gmail, Docs, etc. has demonstrated that for a large range of OLTP workloads, serializable everywhere and also performant /is/ possible to architect for -- with the right combination of both database + application design. That isn't to say it's correct everywhere, but I'd maybe be a little more suspicious of "We want OLTP but we…

[dead]
Post reply on HN