sql: SELECT FOR UPDATE on multi-column-family tables under read committed does not lock completely-NULL column families

[michae2]

In 23.2 we added a new implementation of SELECT FOR UPDATE, which is initially used only for Read Committed isolation (and Serializable isolation when optimizer_use_lock_op_for_serializable is true). As of https://github.com/cockroachdb/cockroach/pull/116170 this implementation of SELECT FOR UPDATE locks all column families of selected rows, with one exception: it does not lock column families of rows for which the value of every column is NULL.

This means, for tables with multiple column families, SELECT FOR UPDATE might not block an UPDATE that changes a NULL value to a non-NULL value.

Here's a demonstration:

SET CLUSTER SETTING sql.txn.read_committed_isolation.enabled = true;

-- A multi-column-family table with a family which can be entirely NULL (f2).
CREATE TABLE abcd (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  d INT NULL,
  PRIMARY KEY (a),
  FAMILY f0 (a, b),
  FAMILY f1 (c),
  FAMILY f2 (d)
);
INSERT INTO abcd VALUES (4, 5, 6, 7), (8, 9, 10, NULL);

-- Under read committed isolation we lock the first row. This locks all column families.
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM abcd WHERE a = 4 FOR UPDATE;

-- Mutations performed by other sessions block.
UPDATE abcd SET d = 17 WHERE a = 4 AND b = 5;

-- The first transaction will not see the update.
-- After the first transaction finishes, the update can proceed.
SELECT * FROM abcd WHERE a = 4;
COMMIT;

-- But if we lock the second row, this locks all column families *except* f2, because d is NULL.
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM abcd WHERE a = 8 FOR UPDATE;

-- Mutations performed by other sessions that only modify the unlocked column family will not block.
UPDATE abcd SET d = 11 WHERE a = 8 AND b = 9;

-- The first transaction will see the update.
SELECT * FROM abcd WHERE a = 8;
COMMIT;

Jira issue: CRDB-34802 Epic CRDB-38938

[michae2]

Note that this would probably need to use single-key predicate locking.