Hacker News Reader

Comment by koolba | Hacker News Reader

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.

Reply on news.ycombinator.com