Other Considerations with SKIP LOCKED

[dfhawthorne]

Is your feature request related to a problem? Please describe.
rponte writes in response to Technical Note On Skip Locked:

One important thing we can’t ignore when using SKIP LOCKED is that it locks the rows during the fetching. I mean, the simple FOR UPDATE locks all rows when we open the cursor, while FOR UPDATE SKIP LOCKED locks only when we fetch the row(s).

It is important to understand that because if we write a query which paginates the results (eg: using rownum or FETCH FIRST) we may get the wrong result or may get an unbalanced distribution of rows among the workers (threads). Indeed, rownum and FETCH FIRST are evaluated before the rows are skipped by SKIP LOCKED, so your code will work but not with the best throughtput you could get.

Does it make sense?

Btw, there’re some good discussions about this trait of SKIP LOCKED:

– https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538644700346644113
– https://stackoverflow.com/questions/6117254/force-oracle-to-return-top-n-rows-with-skip-locked
– https://stackoverflow.com/questions/42899520/does-oracle-skip-locked-prevent-non-repeatable-reads

Describe the solution you'd like
Investigate the issues raised by rponte.

Describe alternatives you've considered
None.

Additional context
None.

[dfhawthorne]

Found an article about SKIP LOCKED in PostgreSQL at How to implement a database job queue using SKIP LOCKED