4 ms·
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 it
by 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 agoI 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 agoPretty 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 agoit 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 agoI 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 agoQueryDsl have it, it also has FOR SHARE.
- tybit 3y agoJOOQ 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 agoActiveRecord supports it: https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pessimistic.html https://api.rubyonrails.org/classes/ActiveRecord/Locking/Pes...
- teaearlgraycold 3y agoSELECT FOR UPDATE SKIP LOCKED is amazing
- ppati000 3y agoAuthor 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 agoIf 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 agoEven better, FOR NO KEY UPDATE is probably a good option here, since no key values are updated.
- layer8 3y agoHowever, 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).