cockroachdb / cockroachdb/cockroach
sql: SELECT FOR UPDATE on multi-column-family tables under read committed does not lock completely-NULL column families
- 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
Assessment
This issue has not been assessed yet.