6 ms·

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.2ndquadr

by evadne 9y ago

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-in-postgresql-9-5 https://blog.2ndquadrant.com/what-is-select-skip-locked-for-... http://www.cybertec.at/skip-locked-one-of-my-favorite-9-5-features http://www.cybertec.at/skip-locked-one-of-my-favorite-9-5-fe... Also, duplicate of: https://news.ycombinator.com/item?id=9576864 https://news.ycombinator.com/item?id=9576864

rpedela 9y ago
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.
ruslan_talpa 9y ago
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.
rosser 9y ago
Pre-9.5, you could use "SELECT ... FOR UPDATE NOWAIT" to achieve the same end.
- malisper 9y ago
  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
  - rosser 9y ago
    > Pre-9.5...
    - malisper 9y ago
      I don't see how NOWAIT gives you what you want here. NOWAIT and SKIPPED LOCKED are completely different.
      - rosser 9y ago
        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?
        malisper 9y ago
        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 https://www.postgresql.org/docs/8.1/static/sql-select.html
        rosser 9y ago
        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.
        shawn-butler 9y ago

by evadne 9y ago

rpedela 9y ago

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.

ruslan_talpa 9y ago

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.

rosser 9y ago

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

malisper 9y ago
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
- rosser 9y ago
  > Pre-9.5...
  - malisper 9y ago
    I don't see how NOWAIT gives you what you want here. NOWAIT and SKIPPED LOCKED are completely different.
    - rosser 9y ago
      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?
      - malisper 9y ago
        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 https://www.postgresql.org/docs/8.1/static/sql-select.html
        rosser 9y ago
        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.
      - shawn-butler 9y ago