[YSQL] Incorrectly blocking conflicting insert on nonexistent row with single-RPC locking in RC and RR isolation levels

[foucher]

Jira Link: DB-7306

Description

In RR and RC isolation modes, when selecting a row that does not exist by PK, an insertion of a row with that PK hangs.

Setup:

CREATE TABLE t (k int PRIMARY KEY);

Session 1:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM t WHERE k=1 FOR UPDATE;

Session 2:

BEGIN ISOLATION LEVEL REPEATABLE READ;
INSERT INTO t VALUES (1);
COMMIT;

Expected (well, prior behavior, and behavior with yb_lock_pk_single_rpc set to false in ysqlsh:

• Session 2 is able to do this.

Result:

• Session 2 is blocked at the time of the INSERT.

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[foucher]

Several of us met and the current plan is the following:

• If the tserver sees row marks on a read in isolation levels RC or RR, it will:
  • Make sure the request is set to select a primary key exactly. If not, it's an error. Since PG only sends a single RPC lock+select on an exact primary key, this is defensive coding in case future PG code is relaxed without the tserver code being updated.
  • Read the row.
    • If the row exists, take the lock on the row.
    • Else there is nothing to lock in RC or RR isolation levels.
• How we lock for the SERIALIZABLE isolation level will not change

At a high level, this does about the same work that the previous code using a LockRows node did--in that case, we did one RPC to read the row, and then if it existed, we would send the row marks to lock a specific ybctid. So this new method just removes the need for the second RPC.

An autoflag is now required, in case PG is upgraded (and would send single-RPC lock+select), but tserver is not. Without an autoflag for this, YugabyteDB would exhibit the behavior described in this bug.

[foucher]

Another set of people met, and the new plan is the following:

• Refactor out the writing of lock intents
• Add a field to the request to write the intent only on returned rows
• Make the read come before the write, based on the field (to be used in RC and RR isolation levels)
• Keep serializable in the same order (write before read)

An autoflag is still required.