Hacker News Reader

Comment by GauntletWizard | Hacker News Reader

#2 is something that nearly no application I know handles, and it saddens me, but not to the extent that it seems to anger many of my fellows. It's perfectly okay to do so for YouTube, it's not worth bothering for many applications of less concurrency than that. Still, it fairly offends me that most applications exist in the middle, where they don't have read isolation, or use logical additions, or have a separate process that cleans up the followers at a point-in-time, so that it's bounded to a day's worth of updates.

View on HN

This is transaction 101. You simply lock the row via a SELECT … FOR UPDATE when you read the value.

Any application that actually cares about consistency of its data must be doing this.

Or just do it one step so the update increments the value in place. That way you have an implicit lock.

However, SELECT FOR UPDATE doesn't lock not-yet-existing rows in PostgreSQL, like it does in MySQL with gap locks. If for example, the transaction performs either an UPDATE or an INSERT based on whether the SELECT FOR UPDATE found or didn’t find a row, then two concurrent executions of that transaction can run into a conflict or duplicate INSERT in PostgreSQL, whereas in MySQL the second execution would block on the SELECT FOR UPDATE of the first execution even when there are no matching rows (but the first execution then will/might create some).

Author here. Thank you (and others) for mentioning SELECT FOR UPDATE. Definitely missing at least a footnote in the article.

Note that "one step" updates (e.g., SET followers_count = followers_count + 1) are still not in place. They are regular transactional updates that rewrite the entire row. Still, they can achieve higher throughput because there is no application/database roundtrip between locking and committing.

If it's not an indexed column (and your page isn't too full), it can do a HOT update, which is effectively the same as an in-place write if you're flushing to disk at page granularity anyway unless you have really wide rows.

I haven't used this, but I see its in Oracle and MySQL - does this exist in Postgresql or is this just the same as wrapping in a transaction where you're selecting on the row by ID or something? Just curious

Pretty much every RDBMS has this: https://www.postgresql.org/docs/current/explicit-locking.htm...

There are multiple flavors in PostgreSQL with different locking semantics. And the exact locking semantics may differ between database(for instance postgres does not have gap locks, while MySQL does) so you'd have to read the docs if that detail matters to you.

it does exist and I think it was even added to the SQL standard in some update

what FOR UPDATE does is (simplified) to lock a mutex for each row the select statement returns which are released once the transaction commits

this has the effect that parallel running transactions have to wait until the new computed value is visible before reading it and in turn there is no problem with lost updates

just to be clear this is a very simplified explanation in many ways

SELECT FOR UPDATE SKIP LOCKED is amazing

Even better, FOR NO KEY UPDATE is probably a good option here, since no key values are updated.

I don't disagree, but it's so uncommonly used, and I don't know any orm that will do it for you- it'd be easy enough to precompile, but that'd require compiler integration. (Or a hit to every select statement - which probably wouldn't matter for most apps!)

JOOQ handled this nicely.

https://www.jooq.org/doc/latest/manual/sql-execution/crud-wi...

ActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...

QueryDsl have it, it also has FOR SHARE.

I'm convinced that this is due to developers over-relying on ORMs instead of delving into raw SQL.

I'll give another, different but somewhat related example: consider a worker that works on a batch of rows, and wants to update each of the rows in the batch, each row with different data for that row. In raw SQL, this is simple enough with the UPDATE FROM VALUES pattern (see e.g. https://stackoverflow.com/a/18799497 ).

There's no support for this in Prisma, for example: https://www.prisma.io/docs/orm/prisma-client/queries/crud#up... . Most developers would wrap prisma.foo.update in an application-level loop, or possibly wrap all the individual updates in a single prisma.$transaction.

I think maybe with Django, you could use a QuerySet with an F expression to do this? [0] Agreed that it's trivial in pure SQL. As a counter though, having worked at places using an ORM (mostly Django), and also where everything was raw SQL, the latter winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later.

I lost any respect for Prisma when I learned it doesn't do JOINs in the DB. [1]

[0]: https://docs.djangoproject.com/en/5.0/ref/models/expressions...

[1]: https://github.com/prisma/prisma/issues/5184

> raw SQL... winds up having WILDLY inefficient schema and queries that the DB team (me) has to fix later.

I won't dispute this, I'll just point out that (a) premature optimization is the root of all evil, (b) because it's raw SQL, it's easier to reason about and fix. Inefficient queries aren't really a problem in the early days when your whole dataset doesn't even add up to a gigabyte.

The queries aren't necessarily a problem early on (although I've seen OFFSET/LIMIT used for pagination, which is just... no), but schema is sticky. Terrible decisions made early on only become harder to fix as the data set grows.

If you start using locking, you'll encounter many more serialization failures. Similarly for higher isolation levels. This means that you need to retry transactions, and doing that can be quite hard. Many years ago, I wrote some helper code to retry transactions on transient failures for PostgreSQL (which was unnecessarily hard at the time because the error codes that are eligible for transaction retry were not documented clearly). But even with that taken care of, you still had to think carefully about non-transactional side effects (such as sending mail) when writing application logic. A neat side effect was that you could restart the PostgreSQL server without impacting running applications (and today, you could probably even kexec a new kernel before the application timeout kicks in).

I suspect with the current preferences to avoid exceptions, writing for automated retry becomes quite a bit harder. And I couldn't really get automated transaction retry to work for SQLite while still caching statement handles, treating them as prepared statements.

That's why automated retry on concurrency failures should be a built-in feature of the database (or at least of the DB client). It should be easy to register your own commit (for side effects that can be deferred until success) or abort (for side effects that can't be deferred but can be undone) handlers for a given transaction.

I don't think you can have automated retry because if the application logic lives outside the database and is not subject to its transaction processing (which is the common programming pattern today, I think), the database has to arrange for a re-run of that logic if any of the observed database state changes (such as the followers_count returned from the SELECT statement in the example). This means that the code implementing the application code has to be ready to execute multiple times without ill effects, and application programmers need to be aware of that.

That's true, that's why the client API should allow you to submit a callable object of some sort (e.g. a C++ lambda) that is automatically wrapped in a DB transaction and allows you to register commit and abort handlers as I described. Here's such an API that I'm currently working on: https://senderista.github.io/atomik-website/.

While I agree, it's a pretty bad example for demonstrating this issue, because an error on "follower_count" will probably not cause any harm (youtube example). Also, the value can be recalculated at all times (maybe not feasable).

Any other example that comes to my mind will not depend on the "state of the apps memory" for a sensitive value, because that would be bad design too. Then the sensitive value must be the aggregate of other values within the database and with each commit, the equation must be balanced.

That's what makes it such a perfect example. It's inconsequential, but also simple. A SQL statement `update follower_count = follower_count +1` is incredibly cheap in SQL, even as part of a transaction. Accepting that the follower counts are laggy is incredibly acceptable. Repeatable read is expensive, but cheap enough for many, many applications.

The problem is that most applications that I've worked on do none of the above - they accept the buggy behavior in the concurrency case. As part of the "post reconciliation" solution, that's fine, but it's a bug that will never get fixed.

Perhaps worst is that there's an entire class of people - probably autistic, and often not people I want to work with for one obvious reason or another[1] - who simply have to be kept in the dark about long-standing minor bugs. These are known inconsistencies in the data that don't matter but that they'll pitch a fit to fix because they can't see.the difference in constraints like "the follower count will be an integer" and "The (recorded, but fundamentally cached) follower count will equal `SELECT COUNT(*) FROM followers WHERE account = ${user}`

[1] Really, just one reason - they reject rhetorical logic, focusing on formal logic for anything that catches their attention, to the point they are happier with atrocities than morality.

The example with follower_count shows how to use the +1 technique to do without locking. That's OK IMHO. It's not possible to use that technique when updating other type of data. Example

  select * from issues where id = 123;
  -- do something in app
  update issues set status = 'done' where id = 123;

There you can lose a status update if two people happen to work on the same issue at the same time with two different ideas of how to update it.

Or the classic bank account balance update. Both must be solved with a SELECT FOR UPDATE. That should be in the demo page of every ORM, to be sure that people that don't know SQL don't make that kind of mistakes.

> The example with follower_count shows how to use the +1 technique to do without locking..

Without explicit pessimistic locking. There are always locks. More guarantees, more locks.

That issue example can be tackled with optimistic concurrency controls depending on the constraints. The issue can be checked out with an UPDATE .. WHERE .. RETURNING ..

Reply on news.ycombinator.com