6 ms·

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

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

koolba 3y ago
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.
- arthurcolle 3y ago
  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
  - Rapzid 3y ago
    Pretty much every RDBMS has this: https://www.postgresql.org/docs/current/explicit-locking.html 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.
  - dathinab 3y ago
    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
- GauntletWizard 3y ago
  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!)
  - NeveHanter 3y ago
    QueryDsl have it, it also has FOR SHARE.
  - tybit 3y ago
    JOOQ handled this nicely. https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/ https://www.jooq.org/doc/latest/manual/sql-execution/crud-wi...
  - izietto 3y ago
    ActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...
- teaearlgraycold 3y ago
  SELECT FOR UPDATE SKIP LOCKED is amazing
- ppati000 3y ago
  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.
  - ndriscoll 3y ago
    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.
- drdaeman 3y ago
  Even better, FOR NO KEY UPDATE is probably a good option here, since no key values are updated.
- layer8 3y ago
  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).
chrisandchris 3y ago
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.
- GauntletWizard 3y ago
  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.
- pmontra 3y ago
  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.
solatic 3y ago
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 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#update-multiple-records 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.
- sgarland 3y ago
  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/#f-expressions https://docs.djangoproject.com/en/5.0/ref/models/expressions... [1]: https://github.com/prisma/prisma/issues/5184 https://github.com/prisma/prisma/issues/5184
  - solatic 3y ago
    > 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.
    - sgarland 3y ago
      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.
fweimer 3y ago
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.
- senderista 3y ago
  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.
  - fweimer 3y ago
    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.
    - senderista 3y ago
      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/ https://senderista.github.io/atomik-website/.

koolba 3y ago

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.

arthurcolle 3y ago
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
- Rapzid 3y ago
  Pretty much every RDBMS has this: https://www.postgresql.org/docs/current/explicit-locking.html 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.
- dathinab 3y ago
  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
GauntletWizard 3y ago
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!)
- NeveHanter 3y ago
  QueryDsl have it, it also has FOR SHARE.
- tybit 3y ago
  JOOQ handled this nicely. https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/ https://www.jooq.org/doc/latest/manual/sql-execution/crud-wi...
- izietto 3y ago
  ActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...
teaearlgraycold 3y ago
SELECT FOR UPDATE SKIP LOCKED is amazing
ppati000 3y ago
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.
- ndriscoll 3y ago
  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.
drdaeman 3y ago
Even better, FOR NO KEY UPDATE is probably a good option here, since no key values are updated.
layer8 3y ago
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).

chrisandchris 3y ago

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.

GauntletWizard 3y ago
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.
pmontra 3y ago
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.

solatic 3y ago

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 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#update-multiple-records 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.

sgarland 3y ago
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/#f-expressions https://docs.djangoproject.com/en/5.0/ref/models/expressions... [1]: https://github.com/prisma/prisma/issues/5184 https://github.com/prisma/prisma/issues/5184
- solatic 3y ago
  > 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.
  - sgarland 3y ago
    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.

fweimer 3y ago

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.

senderista 3y ago
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.
- fweimer 3y ago
  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.
  - senderista 3y ago
    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/ https://senderista.github.io/atomik-website/.