Time series: concurrent single-row SQL INSERTs silently lose samples (sealed-slot lost…
- User
Time series: concurrent single-row SQL INSERTs silently lose samples (sealed-slot lost update)
Summary
Under many concurrent single-row
INSERT INTO <timeseriesType> SET ts=…, <tag>=…, <field>=…statements (each in its own transaction, as happens over the HTTP/wire protocols), the time series engine silently drops samples. The client receives success on every insert (HTTP 200, no exception, noConcurrentModificationException), yet the stored row count comes up short. Confirmed real data loss: a full table scan returns the same short count ascount(*), and aGROUP BY sensor_idshows specific series missing rows.The batched InfluxDB line-protocol path (
POST /api/v1/ts/{db}/write, which appends many samples in one engine call/transaction) is not affected. Single-threaded inserts are not affected.Impact
Silent, unacknowledged data loss on the time series write path under concurrency. Severity is high because there is no error surfaced to the client — an application doing concurrent per-row ingestion over SQL (HTTP or gRPC) loses samples without any signal.
Environment
- Observed on
26.6.1-SNAPSHOT(== thearcadedata/arcadedb:latestimage at time of writing). - Reproduced both embedded (in-process engine) and over HTTP against the Docker image.
Reproduction
A. Embedded (fast, deterministic at high concurrency)
A disabled regression test is included in PR #4450:
engine/src/test/java/com/arcadedb/engine/timeseries/TimeSeriesConcurrentInsertTest.javaIt creates a
TIMESERIES TYPE … SHARDS 1, runs 48 threads × 5000 single-rowINSERTs (each in its owndatabase.transaction(...)), then assertscount(*) == 48*5000. Result: e.g. 239911 / 240000 — 89 samples lost, with no exception and no failed transaction.Enable it by removing the
@Disabledannotation.B. Over HTTP (against the published image)
CREATE TIMESERIES TYPE sensor TIMESTAMP ts TAGS (sid STRING) FIELDS (v DOUBLE) SHARDS 1Then 16 threads, each POSTing 5000 distinct single-row inserts to
POST /api/v1/command/{db}:{"language":"sql","command":"INSERT INTO sensor SET ts=<unique>, sid='s<thread>', v=<i>"}SELECT count(*) FROM sensorreturns short by 10–22 out of 80000 across runs.Observations on the trigger
- Concurrency level (thread count) is the driver, not total row count. 5 threads often look clean; 16–48 threads reliably lose.
SHARDS 1maximizes it (all appends contend on one shard).- Single-threaded never loses.
- The unfiltered
SELECT count(*)reflects the true (short) count; a full row scan agrees with it (so it is real loss, not acount(*)artifact).
Root cause analysis
The write position for a sample is a read-modify-write of the per-data-page sample count. In
TimeSeriesBucket.appendSamples(engine/.../engine/timeseries/TimeSeriesBucket.java):final int sampleCountInPage = dataPage.readShort(DATA_SAMPLE_COUNT_OFFSET) & 0xFFFF; // read slot final int rowOffset = DATA_ROWS_OFFSET + sampleCountInPage * rowSize; dataPage.writeLong(rowOffset, timestamps[i]); // write row at slot ... dataPage.writeShort(DATA_SAMPLE_COUNT_OFFSET, (short) (sampleCountInPage + 1)); // advance slotTimeSeriesShard.appendSamplesserializes appends per shard with aappendLockaround a nesteddb.begin()/append/db.commit()ongetWrappedDatabaseInstance()(and the comment there notes the lock exists specifically to avoid a commit-timeConcurrentModificationExceptionon page 0). The append commit is nested inside the enclosing request transaction (LocalDatabase.begin()pushes a nestedTransactionContextwhen a transaction is already active), and the durable publish is effectively deferred to the outer transaction commit (intentionally, so the bucket page writes ship to followers via the parent Raft WALTX_ENTRY— the code references issue #4382). That outer commit happens outsideappendLock.A temporary collision detector added to
appendSamples(recordingfileId:pageNumber:slotper append) confirmed the mechanism: a following serialized append reads a stalesampleCountInPage— the prior append'scount → N+1increment is not visible — so two appends compute the samerowOffsetand write the same slot N; the later commit overwrites the earlier row. The page version advances normally, andPageManager.updatePageVersion's version check does not raise a conflict for these commits, so the existing outer-transactionretriesnever kick in.Sample collision-detector output (48-thread embedded run):
DIAG COLLISION slot=1:1:0 pageVer=239 prevThread=74 curThread=88 DIAG COLLISION slot=1:1:1 pageVer=240 prevThread=68 curThread=87 DIAG COLLISION slot=1:1:2 pageVer=241 prevThread=42 curThread=68 ...(slot reused across threads; page version high and advancing while the slot/count read is stale.)
Why the batched path is safe
TimeSeriesEngine.appendBatchgroups all of a shard's samples into a single transaction per shard, so there is never a second concurrent transaction reading a stale slot on the same page.Relevant code
engine/.../query/sql/executor/SaveElementStep.java—saveToTimeSeriesroutes a SQLINSERTintoengine.appendSamples.engine/.../engine/timeseries/TimeSeriesEngine.java—appendSamples(per-row) vsappendBatch(line protocol).engine/.../engine/timeseries/TimeSeriesShard.java—appendSamples(appendLock+ nested begin/commit on the wrapped instance).engine/.../engine/timeseries/TimeSeriesBucket.java—getOrCreateActiveDataPage+appendSamples(theDATA_SAMPLE_COUNT_OFFSETslot RMW).engine/.../engine/PageManager.java—updatePageVersion(the version conflict check that is not catching this).
Suggested direction (for discussion)
The core tension is that
appendLockserializes the in-memory append but the durable publish is deferred to the enclosing transaction's commit (kept that way so writes ship via the parent Raft WAL). Any fix needs to make the slot read-modify-write atomic with respect to the actual publish without breaking HA replication — e.g. ensuring the per-shard append publishes/serializes the slot under the lock, or ensuring the concurrent same-page commits raise a conflict so the existing outer-transaction retry path heals them. This is a delicate change to the transaction/HA path and warrants careful design plus HA + performance verification.Notes
- A reliable embedded regression test is included (disabled) in PR #4450; enable once fixed.
- The time series load tests in the same PR catch this at scale on the SQL protocols.
Interface
No new interfaces are introduced.
- Observed on
- Assistant
Now let me look at how nested transactions work in
LocalDatabase: - Assistant
Now let me enable the regression test and run it to reproduce:
- Assistant
Now add the helpers to
TimeSeriesBucket: - Assistant
Now compile and run the regression test:
- Assistant
Passes. Let me run it twice more to make sure it's not luck (before the fix it failed 3/3), then strengthen the existing append+compact test with exact-count assertions:
- Assistant
Passes, but I want to verify the strengthened test actually catches the original bug. Let me temporarily revert just the shard fix and confirm both tests fail:
- Assistant
The strengthened test catches the bug against the unfixed code (297/300). Now restore the fix:
- Assistant
The fix is intact. Now run the full time series test package to check for regressions:
- Assistant
All server-side TS tests pass. Final verification — no leftover debug code, and the complete diff:
- Assistant
Let me update the now-inaccurate comment in the regression test to reflect the actual root cause:
- Assistant
All green. Let me save one environment note for future sessions:
- Assistant
Done. The fix is implemented, verified, and all related tests pass.
Summary
Actual root cause (differs from the issue's theory): The per-shard
appendLockpath is sound - I instrumentedTimeSeriesBucket.appendSamplesand confirmed that under the lock, every append sees the previous append's committed slot and page versions (no stale slot read, no header version regression). The loss comes from the lock-free compaction window:TimeSeriesMaintenanceSchedulerrunscompactAll()5 seconds after the type is created (and every 60s), so it races the concurrent inserts.- Compaction Phase 4a (under the write lock) snapshots pages up to
phase4aPageCount, including the partial active page. - Phase 4b runs lock-free while concurrent appends keep filling that same page.
- Phase 4c only read pages
phase4aPageCount + 1 .. finalPageCount, thenclearDataPages()wiped the bucket - silently discarding every row appended to the last snapshot page during Phase 4b (up to ~one page of rows, matching the observed 74-139 losses per run).
The issue's "slot collision" diagnostic output (
slot=1:1:0 pageVer=239) was actually legitimate page-1 reuse afterclearDataPages(), not an append-path lost update. This also explains why the batched line-protocol path appeared unaffected and why the trigger correlates with concurrency (more appends land in the Phase-4b window).Fix (compaction path only; no change to the append/transaction/HA machinery):
TimeSeriesShard.compactInternalPhase 4a now records the sample count of the last snapshot page (phase4aLastPageSamples) in the same locked read-only TX.- Phase 4c reads the delta rows
[phase4aLastPageSamples .. count)of that page plus any whole new pages, so nothing is lost or duplicated before the clear. Under HA this stays inside the existingrunWithCompactionReplicationrecording session, so replication semantics are unchanged. TimeSeriesBucketgained two read-only helpers:getPageSampleCount(pageNum)andreadPageRowsForCompaction(pageNum, fromRow).
Verification:
- Enabled the disabled regression test
TimeSeriesConcurrentInsertTest(48 threads × 5000 single-row INSERTs): failed 3/3 before (239861-239993 / 240000), passes 3/3 after. - Strengthened
TimeSeriesConcurrentAppendCompactTestto compact continuously during writes and assert an exact count (was>= 100); confirmed it fails against the unfixed code (297/300) and passes with the fix. - All 236 engine time series tests, 129 promql/codec/simd tests, and server-side
TimeSeriesQueryHandlerIT+PostTimeSeriesWriteHandlerITpass.
Not committed, per project instructions - ready for your review.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.
Identical first prompt
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- composer-2.5Compare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- gpt-5.6-solCompare side by side →
- fable-5Compare side by side →