How can you use 'For update skip locked' in postgres without locking rows in all tables used in the query?

Question

When you want to use postgres's SELECT FOR UPDATE SKIP LOCKED functionality to ensure that two different users reading from a table and claiming tasks do not get blocked by each other and also do not get tasks already being read by another user:

A join is being used in the query to retrieve tasks. We do not want any other table to have row-level locking except the table that contains the main info. Sample query below - Lock only the rows in the table -'task' in the below query

SELECT v.someid , v.info,  v.parentinfo_id, v.stage  FROM task v, parentinfo pi  WHERE v.stage = 'READY_TASK' 
             AND v.parentinfo_id = pi.id 
             AND pi.important_info_number = ( 
             SELECT MAX(important_info_number) FROM parentinfo )
              ORDER BY v.id limit 200 for update skip locked;

Now if user A is retrieving some 200 rows of this table, user B should be able to retrieve another set of 200 rows.

EDIT: As per the comment below, the query will be changed to :

SELECT v.someid , v.info,  v.parentinfo_id, v.stage  FROM task v, parentinfo pi  WHERE v.stage = 'READY_TASK' 
             AND v.parentinfo_id = pi.id 
             AND pi.important_info_number = ( 
             SELECT MAX(important_info_number) FROM parentinfo)  ORDER BY v.id limit 200 for update of v skip locked;

How best to place order by such that rows are ordered? While the order would get effected if multiple users invoke this command, still some order sanctity should be maintained of the rows that are being returned.

Also, does this also ensure that multiple threads invoking the same select query would be retrieving a different set of rows or is the locking only done for update commands?

Answer 1

Just experimented with this a little bit - multiple select queries will end up retrieving different set of rows. Also, order by ensures the order of the final result obtained.