Under read-committed isolation, it's possible for a SFU statement to error with WriteTooOldError. The error message mentions automatic retries. This is surprising behavior because we would expect the SFU to block until a lock on a row can be acquired, not retry and eventually error.

I've built a simple reproduction. It uses a schema and concurrent transactions of the form:

CREATE TABLE t (k INT PRIMARY KEY, v INT);
INSERT INTO t VALUES (1, 1);

BEGIN;
SELECT 1; -- I think this could be any SQL statement, or multiple.
SELECT * FROM t WHERE k = 1 AND v > 0 FOR UPDATE;
UPDATE t SET v = v + 1 WHERE k = 1;
COMMIT;

See the reproduction here: https://gist.github.com/mgartner/bd034885fec505df4d532a268e101116

The query plan of the SFU, under read-committed isolation, looks like:

  • lookup join (semi)
  │ table: t@t_pkey
  │ equality: (k) = (k)
  │ equality cols are key
  │ locking strength: for update
  │ locking durability: guaranteed
  │
  └── • filter
      │ filter: v > 0
      │
      └── • scan
            missing stats
            table: t@t_pkey
            spans: [/1 - /1]

The locks are acquired in the lookup join. The scan at the bottom is non-locking. This ensures that we don't lock rows that do not satisfy the v > 0 filter.

The locking lookup-join internally results in a WriteTooOldError when another transaction has written the row at a timestamp after the timestamp of the scan. This force the SFU to be automatically retried. When the number of retries exceeds max_retries_for_read_committed (with a default of 10), the SFU statement results in an error.

Jira issue: CRDB-49877