3 ms·
I don't see how NOWAIT gives you what you want here. NOWAIT and SKIPPED LOCKED are completely different.
by malisper 9y ago
I don't see how NOWAIT gives you what you want here. NOWAIT and SKIPPED LOCKED are completely different.
- rosser 9y agoThe 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 agoAFAICT 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 agoYou'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 agoThis 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"