51–60 of 60 posts
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…
Earlier quoted context omitted.
Is there any good reason to use stored procedures in 2026?
I'd think so. Stored procedures let you do multi-statement sequences in fewer round trips. In 2026 larger systems are as likely as ever to run PostgreSQL on a different machine (or machines) than the application server. While latency between the two generally goes down over time, it's still not nothing. You may care about the latency of individual operations or the throughput impact of latency while holding a lock (s…
* Good database drivers will let you pipeline multiple queries concurrently (esp. in languages with async support), effectively eliminating the _N_x roundtrip cost (you can even execute them in parallel if you use multiple connections, not that I recommend doing that). But obviously this is only doable where the queries are independent of one another; I use this mainly to perform query splitting efficiently if the join key is already known.
* These days databases are often effectively versioned alongside the code anyway, at least for either smaller projects that "own" the database, eliminating the biggest issue with stored procedures.
In worst case scenario you can’t still get flaky test, right? Single thread runtime that will allow the queries to interleave sometimes and sometimes work correctly - talking about variant without "FOR UPDATE".
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…
Javascript developers learn kindergarten basics of transactions and SQL. LOL. Is it the camp "we don't need a degree to be programmers"?
> The transaction didn't help. Postgres's default isolation level is READ COMMITTED — each statement sees all data committed before that statement started.
It'd be interesting to see a version of this that tries all the different interleavings of PostgreSQL operations between the two (or N) tasks. https://crates.io/crates/loom does something like this for Rust code that uses synchronization primitives.
It uses generators and their yield as the yield point (and supports running arbitrary functions under a debugger)
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)...
Earlier quoted context omitted.
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 w…