cockroachdb / cockroachdb/cockroach

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

Open
#116,836 2 comments 0 reactions 0 assignees View on GitHub
A-read-committed C-bug docs-done docs-known-limitation T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

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:

```sql
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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.