Hacker News Reader

Comment by karmakaze | Hacker News Reader

For complete/correctness:

  select balance from accounts where name = 'A' for update;

> Now, other transactions won’t be able to read this row until our transaction is committed or rolled back (for update can only be used inside a transaction).

This is true for SERIALIZABLE isolation. Depending on the configured isolation level (e.g. READ COMMITTED aka snapshot) other concurrent transactions can read (but not update) the locked row, which can lead to write skew.

View on HN

ETA: I misread, I thought you were referring to the table lock section, but you were referring to the row lock section. You're correct, FOR UPDATE locks do not prevent concurrent reads, which could cause a concurrency problem if you're not very careful (and Murphy's law applies).

---

The article is correct. That section contains this snippet:

    lock table accounts;

The default lock mode for LOCK is ACCESS EXCLUSIVE, which will prevent concurrent reads.

    ACCESS EXCLUSIVE (AccessExclusiveLock)

    Conflicts with locks of all modes [...]. This mode guarantees that the holder is the only transaction accessing the table in any way.

https://www.postgresql.org/docs/current/explicit-locking.htm...

The part that has "for update" doesn't have "lock table".

My bad, thanks.

For update actually does block concurrent SELECT FOR UPDATES, so the article is correct

Just so everyone's in agreement. Yes, FOR UPDATE blocks concurrent SELECT...FOR UPDATE (as well as SELECT...FOR SHARE/IN SHARE MODE). What the article said though was reads:

> Now, other transactions won’t be able to read this row until our transaction is committed or rolled back (for update can only be used inside a transaction).

Finally if transaction isolation is SERIALIZABLE, then a row lock can block normal SELECT reads. This is a consequence of SERIALIZABLE isolation preventing write skew. Transactions can be run in parallel, but there has to be some ordering of transactions that produces the same result as if each transaction was one run one at a time.

FOR UPDATE locks the row for other blocking transactions (including another select for update) The weaker form that only locks updates and deletes is called FOR SHARE. Transaction isolation levels do not make a difference here.

https://www.postgresql.org/docs/current/explicit-locking.htm...

So the article is correct that SELECT FOR UPDATE will ensure that another concurrent SELECT FOR UPDATE transaction never acquire the same row (it will block), though nothing prevents other non-blocking selects to query this row concurrently.

You can think of it as usual locks - only the threads that explicitly use the same lock have the mutual exclusion guarantees. If there’s another thread that does not acquire the lock and tries to access the critical section - it will be able to do so.

Reply on news.ycombinator.com