sql: SELECT FOR UPDATE does not prevent insertion of new rows (phantoms)

[michae2]

Regardless of isolation level, SELECT FOR UPDATE and SELECT FOR SHARE statements in CockroachDB do not prevent insertion of new rows matching the search condition (a.k.a. phantoms). This is the same as PostgreSQL behavior at all isolation levels, but can be a little surprising.

Here's a demonstration:

CREATE TABLE ab (a INT PRIMARY KEY, b INT);
INSERT INTO ab VALUES (0, 0), (1, 1), (2, 2);

-- first connection: hold open a transaction with exclusive locks
BEGIN;
SELECT * FROM ab WHERE a > 0 FOR UPDATE;

-- second connection: these will not block, as expected
UPDATE ab SET b = b + 10 WHERE a = 0;
INSERT INTO ab VALUES (-1, -10);

-- third connection: this will block, as expected
UPDATE ab SET b = b + 10 WHERE a = 1;

-- fourth connection: these will *not* block, which matches PostgreSQL behavior but could be surprising
INSERT INTO ab VALUES (3, 30);
UPDATE ab SET a = a + 10 WHERE a = 0;

Other databases have different behavior. E.g. in MySQL using InnoDB, under REPEATABLE READ isolation and higher, the final insert and update will block thanks to InnoDB's next-key locks (a.k.a. gap locks or predicate locks).

As mentioned here, we're planning to add support for single-key predicate locking (i.e. on Get) in order to allow uniqueness checks on regional by row tables under read committed isolation. That won't be sufficient to change this behavior of SELECT FOR UPDATE, however. To make SELECT FOR UPDATE prevent phantoms in all cases, we would need multi-key predicate locking (i.e. predicate locking on Scan).

Jira issue: CRDB-36814

[michae2]

(This is documented in the When to use locking reads section of our read committed docs.)