no it may not

this is fully standard compatible behavior and even expected behavior for _any_ SQL database implementing a read committed transaction insulation level

also it's a problem which on a theoretically level is not solvable with how SQL works, hence why on stricter isolation levels like serializable committing the transaction in such a situation will fail (in any SQL database) and needs to be retired. (Through in some unusual setups the db might be able to translate retry the transaction by itself, through at the cost of a ton of drawbacks)

Anyway reading the postgres documentation is always a good idea, it's not perfect but pretty good.

In more complex scenarios, FOR UPDATE is the solution.

It gets much more complicated when you consider full page writes, checkpoints, HOT, and multiple rows being updated at once.

For #3 it really depends on a lot of factors what the best approach is and if it's even worth your time.. But a typical approach that isn't mentioned is to just make sure you acquire locks in a deterministic order between transactions you don't want to deadlock each other. This will reduce concurrency(which is the entire point of the deadlock detection feature), but you can push the queue into the DB(behind the lock) which will minimize latency if you are done round-tripping.

The 28 is a factor (×).

The OS doesn't write bytes to the storage; it writes blocks. For modern drives the sector size is 4k, and the block io size is likely to be 4k unless utilizing 512b emulation.

So, the OS will always send 4k minimum writes to the storage device. In this case updating just the 8bytes would still result in a 4k write; at least to the wal then again to update the page plus index updates and etc.

    UPDATE user_profile
    SET followers_count = 0
    WHERE followers_count IS NULL
    LIMIT 10000

This makes it much easier to make small-ish batch updates and avoid locking all rows in the table.

    WITH batch AS (
      SELECT id FROM user_profile
      WHERE followers_count IS NULL
      LIMIT 10000
    )
    UPDATE user_profile
    SET followers_count = 0
    FROM batch
    WHERE user_profile.id = batch.id

https://news.ycombinator.com/item?id=38684447

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.

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.

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.

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

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

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

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.

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.

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.

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

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.

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

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.

This effectively changed the tone from an informational article to a hit piece. This is a huge accusation which is plain wrong.