Hacker News Reader

Comment by evadne | Hacker News Reader

I noticed that this article was written in 2015 and I wonder whether SKIP LOCKED in Postgres 9.5 could have changed the entire situation,

https://blog.2ndquadrant.com/what-is-select-skip-locked-for-...

http://www.cybertec.at/skip-locked-one-of-my-favorite-9-5-fe...

Also, duplicate of: https://news.ycombinator.com/item?id=9576864

View on HN

I came to say the very same thing. There may still be problems with using SKIP LOCKED but it appears the core problem mentioned in the blog post would be solved by it.

certainly, would have helped but then we would not have had such great article about inner workings of PostgreSQL (especially since it applies to more the queues). If this problem would have been encountered by an average team (like me) it would have been impossible to fully understand what's going on so I am glad this article exists.

Pre-9.5, you could use "SELECT ... FOR UPDATE NOWAIT" to achieve the same end.

Uh... NOWAIT will error if the query encounters a locked row and rollback the transaction. SKIP LOCKED will make locked rows invisible to the current transaction, but will not cause the transaction to be rolled back. SKIP LOCKED effectively provides equivalent behavior to the recursive CTE given in the post

> Pre-9.5...

I don't see how NOWAIT gives you what you want here. NOWAIT and SKIPPED LOCKED are completely different.

The clause "SKIP LOCKED" did not exist until 9.5.

Before that version, the behavior of silently skipping rows that had outstanding locks against them was specified by using "NOWAIT".

The decision of which specific clause to use is driven by which version of PostgreSQL you're using.

How much more plainly can I put that?

AFAICT the behavior of NOWAIT has always been the same. The earliest mention of NOWAIT I could find was in Postgres 8.1[0]:

> To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. SELECT FOR UPDATE NOWAIT reports an error, rather than waiting, if a selected row cannot be locked immediately.

[0] https://www.postgresql.org/docs/8.1/static/sql-select.html

You're right. With a super-contrived test case, I got the described behavior.

Somehow, in all the the hundreds of millions (guesstimate) of rows I've used SELECT FOR UPDATE NOWAIT to process without lock conflicts, I've never actually gotten the error. At a guess, because I've generally always also used a LIMIT clause, and there have always been enough unlocked rows available to satisfy that.

Apologies for the noise.

This seems quite false. NOWAIT would error in the face of locked rows. SKIP LOCKED skips without error.

"With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. Note that NOWAIT applies only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way"

Reply on news.ycombinator.com