[YSQL] Explicit row-level locking should only lock what is returned to the client

[pkj415]

"When a locking clause appears at the top level of a SELECT query, the rows that are locked are exactly those that are returned by the query; in the case of a join query, the rows locked are those that contribute to returned join rows." - this is from Pg docs - https://www.postgresql.org/docs/12/sql-select.html

The below example shows this semantic in action. In YB, we lock more rows than what is returned to the user. Though this won't result in a correctness issue, to match Pg usability, we should lock only what is returned.

create table employees (k int primary key, profession text, email text);
create table physician (k int primary key, email text);
insert into employees values (1, 'sales', 'salesman1@xyz.com') ;
insert into employees values (2, 'sales', 'salesman2@xyz.com') ;
insert into employees values (3, 'physician', 'phy1@xyz.com') ;
insert into employees values (4, 'physician', 'phy2@xyz.com') ;
insert into physician values (1, 'phy1@xyz.com');
insert into physician values (2, 'phy2@xyz.com');

session1> begin transaction isolation level repeatable read;
session2> begin transaction isolation level repeatable read;

session1> select * from physician, employees where employees.profession = 'sales' and
employees.email = physician.email for update;
-- no rows will be returned to user, hence nothing locked in Pg. In YB we lock all rows since seq scan reads all rows.
Filtering removes rows later, but by that time we have already locked rows in YB.

session2> select * from employees for update; -- doesn't block in Pg (blocks in YB).

The cause of this is that even in REPEATABLE READ isolation level, we take coarser locks than just locks for specific rows. This should not be done in any isolation level except SERIALIZABLE (where coarser locks are required to ensure predicate locking to block new rows to be inserted that match the predicate of an ongoing txn's read).

NOTE: Locking only rows returned to user as compared to coarser chunks (i.e., by locking a prefix of the pk) might seem to be inefficient due to the many locks that we now have to take. But note coarser locks can also hurt performance by locking what isn't to be locked. So, both have their disadvantages based on the specific workload; but locking only rows returned to user gives us behaviour same as Postgres. This will serve as the motivation to switch to only locking rows that are returned to user rather than coarser locks.