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

31–40 of 60 posts

Re: Testing Postgres race conditions with synchronization barriers

#31

Post by theptip »

I’ve idly toyed with this problem as well, I think there’s a good opportunity to build a nice framework in Python with monkeypatching (or perhaps in other languages using DB/ORM middleware) so you don’t need to modify the code under test.

I think you can do better than explicit barrier() calls. My hunch is the test middleware layer can intercept calls and impose a deterministic ordering.

(There are a few papers around looking into more complex OS level frameworks to systematically search for concurrency bugs, but these would be tough to drop into the average web app.)

Re: Testing Postgres race conditions with synchronization barriers

#32

Post by reitzensteinm »

lirbank wrote: post #30
reitzensteinm wrote: post #29

Earlier quoted context omitted.

Martin Kleppmann has this tool that's quite relevant: https://martin.kleppmann.com/2014/11/25/hermitage-testing-th...

Oh that is super cool. Great prior art to study in combo with Loom. Very excited to dig in - imagine if there was an easy-to-use data race tester where you didn't have to figure out the interleaving points up front? Just point it at your code and let it find them. Exciting.

Loom does exhaustive search, with clever methods to prune it. On real world programs, you have to set a limit to that because it obviously grows extremely quickly even with the pruning.

I've built something similar to Loom, except it's more focused on extensively modeling the C++11/Rust memory model (https://github.com/reitzensteinm/temper). My experience is that fairly shallow random concurrent fuzzing yields the vast majority of all concurrency bugs.

Antithesis (https://antithesis.com/) are probably the leaders of the pack in going deeper.

Re: Testing Postgres race conditions with synchronization barriers

#33

Post by shshshsjjjj »

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…

> no locks in you backend

PG is still handling the locks for you, so this isn’t like a bulletproof solution and - like always - depending on your use case, scale, etc this may or may not work.

> No matter the load and concurrent users it will work like magic

Postgres will buckle updating a single row at a certain scale.

————-

Regardless, this article was about testing a type of scenario that is commonly not tested. You don’t always have a great tool like PG on hand that gives you solutions so this testing isn’t needed.

Re: Testing Postgres race conditions with synchronization barriers

#34

Post by theptip »

throwaway2ge5hg wrote: post #15

Postgres has SERIALIZABLE transaction isolation level. Just use it and then you never have to worry about any of these race conditions. And if for some reason you refuse to, then this "barrier" or "hooks" approach to testing will in practice not help. It requires you to already know the potential race conditions, but if you are already aware of them then you will already write your code to avoid them. It is the non-o…

Not a silver bullet. When you use serializable you have more opportunities for deadlocks (cases which would otherwise be logic errors at weaker isolation levels).

Serializable just means that within the transaction your logic can naively assume it’s single threaded. It doesn’t magically solve distributed system design for you.

“Just use random testing” isn’t really an answer. Some race conditions only show up with pathological delays on one thread.

Re: Testing Postgres race conditions with synchronization barriers

#35

Post by egedev »

We hit exactly this kind of race condition in our Go + Postgres SaaS when handling concurrent waitlist signups. Two requests would read the current count, both pass the limit check, and both insert — exceeding the waitlist cap.

Ended up using SELECT FOR UPDATE on the waitlist row before the count check. Simple but effective. The barrier testing approach described here would have caught this much earlier in development instead of discovering it under load.

One thing I'd add: in Go, it's tempting to handle this at the application level with mutexes, but that breaks the moment you have multiple instances. Pushing the serialization down to Postgres is almost always the right call for correctness.

Re: Testing Postgres race conditions with synchronization barriers

#36

Post by lirbank »

egedev wrote: post #35

We hit exactly this kind of race condition in our Go + Postgres SaaS when handling concurrent waitlist signups. Two requests would read the current count, both pass the limit check, and both insert — exceeding the waitlist cap. Ended up using SELECT FOR UPDATE on the waitlist row before the count check. Simple but effective. The barrier testing approach described here would have caught this much earlier in developmen…

Hey, thanks for sharing this - these bugs are so easy to miss because everything works fine until you get real concurrent traffic. And yeah, the moment you have multiple instances, app-level mutexes can't save you.

Re: Testing Postgres race conditions with synchronization barriers

#37

Post by LgWoodenBadger »

throwaway2ge5hg wrote: post #15

Postgres has SERIALIZABLE transaction isolation level. Just use it and then you never have to worry about any of these race conditions. And if for some reason you refuse to, then this "barrier" or "hooks" approach to testing will in practice not help. It requires you to already know the potential race conditions, but if you are already aware of them then you will already write your code to avoid them. It is the non-o…

“because it is possible to have race conditions surface even within a single database query.”

This brought back awful memories of MS SQLServer and JDBC. Way back when, maybe Java 1.5 or so, SQLServer would deadlock between connections when all they were doing was executing the exact same statement. Literally. Not the same general statement with different parameters.

Re: Testing Postgres race conditions with synchronization barriers

#38

Post by valenterry »

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…

> Shameless plug: learn your tool. Don’t approach Postgresql/Mssql/whathaveyousql like you’re a backend engineer.

Erm, knowing and understanding how to use your database is a bread and butter skill of a backend engineer.

Re: Testing Postgres race conditions with synchronization barriers

#40

Post by fake-name »

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.

Post reply on HN