Concurrent lightweight DELETE can leave a row (MergeTree count=1 after deleting the whole table) #2331

✅ I checked the Altinity Stable Builds lifecycle table, and the Altinity Stable Build version I'm using is still supported.

Type of problem

Bug report - something's broken (wrong remaining row count after concurrent lightweight delete)

Describe the situation

Two concurrent DELETE statements that target disjoint rows on a MergeTree table can finish with mutations_sync=2 and no exception, yet one row is still visible. After deleting the entire table the test sees SELECT count(*) = 1 instead of 0.

The default lightweight_delete_mode is still alter_update, so each DELETE is ALTER TABLE ... UPDATE _row_exists = 0 WHERE .... update_parallel_mode=auto does not serialize two updates of _row_exists (it only serializes updated-vs-used column dependencies). Losing one in-flight mutation's mask leaves exactly the rows that mutation owned.

This is a product correctness race, not a test-logic error. The TestFlows scenario partitions the 1000 unique (id, x) rows into groups of 1–50, then two parallel steps pop() groups under a lock — each row is deleted exactly once. Every DELETE returned exit 0.

This issue:

• Returns wrong results (a row that every DELETE claimed to remove is still visible)
• Does not throw; both mutations complete with mutations_sync=2
• Is pre-existing / flaky — not introduced by PR #2300. The same leftover-row assertion already failed on scheduled HEAD and on the 26.6.2.20001.altinityantalya release image
• Hits CAS jobs more often than plain local MergeTree, but also fails without CAS
• First seen 2026-09-03; 5 fails / 322 OK in the last 30 days (1.5%)

How to reproduce the behavior

Environment

• Version: 26.6.2.20001.altinityantalya (also docker://clickhouse/clickhouse-server:head-alpine)
• Build type: release / binary (not sanitizer-specific)

Option 1: TestFlows scenario (the CI hit)

python3 lightweight_delete/regression.py --local \
  --clickhouse-binary-path <path-or-package> \
  --only '/lightweight delete/concurrent delete/MergeTree/random delete entire table without overlap'

Source: lightweight_delete/tests/concurrent_delete.py (random_delete_entire_table_without_overlap → random_delete_percentage_of_the_table_without_overlap with percent_to_delete=100, partitions=10, block_size=100).

Option 2: SQL sketch of the same coverage

The table is compact 100-row parts (PARTITION BY id ORDER BY id). Two clients issue overlapping-in-time, non-overlapping-in-rows deletes:

CREATE TABLE t (id Int64, x Int64) ENGINE = MergeTree PARTITION BY id ORDER BY id;

-- 10 partitions × 100 unique rows
INSERT INTO t SELECT number % 10, intDiv(number, 10) FROM numbers(1000);

-- Session A and session B, in parallel, each with mutations_sync=2.
-- Partition the 1000 (id, x) pairs into disjoint groups of 1–50 rows and
-- DELETE FROM t WHERE (id = … AND x = …) OR …   -- each pair exactly once.
-- Default lightweight_delete_mode = alter_update (do not set it).

SELECT count(*) FROM t;  -- expected 0; observed 1 on a miss

This is a race. A single sequential run will pass. Repeat the concurrent deletes.

Expected behavior

After every row has been targeted by exactly one DELETE ... SETTINGS mutations_sync=2, SELECT count(*) is 0.

Actual behavior

On release builds

All DELETEs succeed. The count assertion fails:

assert (
    r.output
    == f"{block_size * partitions * (100 - percent_to_delete) // 100}"
), error()

r.output = '1'
expected = '0'

From cas_s3_cache_lightweight_delete_2 on 2026-09-09 (18s 715ms):

✘ 18s 715ms [  Fail  ] /lightweight delete/concurrent delete/MergeTree/random delete entire table without overlap
AssertionError: Oops! Assertion failed
  assert r.output == '0'
  r.output = '1'

Same signature on cas_lightweight_delete_2 the same run (15s 711ms, count 1).

Only this leaf failed in those jobs (499 other scenarios OK in the suite on the S3-cache run). Other engines skip the exact-count assert; the overlapping twin can hide a lost mutation because rows are targeted twice.

Root cause analysis

Each lightweight delete is ALTER TABLE ... UPDATE _row_exists = 0 WHERE … on compact parts. Two such mutations in flight are not serialized by update_parallel_mode=auto (hasConflict in PatchParts/PatchPartsLock.cpp compares updated vs used columns; two _row_exists updates do not conflict). Mutation squash keeps them as separate stages, so last-writer-wins squash is not the likely mechanism.

Losing one mutation's mask leaves exactly that mutation's rows. A leftover count of 1 matches a 1-row delete group (the generator uses randint(1, 50)).

Not enough for a pin-the-line fix yet: leftover (id, x), system.mutations, and system.parts were not captured on these runs.

Additional context

CI failure (latest, PR #2300 — did not introduce this)

• Job: RegressionTestsRelease / CASLightweightDelete (2) and CASS3CacheLightweightDelete (2)
• Branch: feature/antalya-26.6/CAS-improvements (base antalya-26.6)
• Commit: d876cda683f06972f54470910c0366a93002671d
• Version: 26.6.2.20001.altinityantalya
• Run: https://github.com/Altinity/ClickHouse/actions/runs/34294710256
• Jobs: cas_lightweight_delete_2, cas_s3_cache_lightweight_delete_2
• Report: lightweight_delete2/report.html
• JSON browser: PR 2300

On the same SHA, Common lightweight_delete (no CAS) passed on x86 and aarch64 (7/7 OK for this scenario across #2300 packages).

Other hits (same assertion, not this PR)

Flakiness data

• Fail rate: 5 Fail / 322 OK (1.5%) over the last 30 days; all 5 fails are in the last 7 days
• 26.6.2.20000.altinityantalya: 101 / 101 OK (no hits)
• 26.6.2.20001 / HEAD: the five fails above
• CAS vs non-CAS (30d): 3 Fail / 8 CAS runs vs 2 Fail / 319 non-CAS
• Affected: x86_64 primarily (one HEAD aarch64 hit); analyzer on
• Pattern: consistent leftover-row count assertion (1 vs 0), not varying infra errors

Notes

• Suite does not set lightweight_delete_mode
• Overlapping concurrent-delete twin and 25/50/75% without-overlap variants have not been the canary (overlap double-targets rows; smaller percents issue fewer mutations)
• Only MergeTree asserts the exact remaining count