ISSUE DESCRIPTION Time series: concurrent single-row SQL INSERTs silently lose samples…
- User
ISSUE DESCRIPTION
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.
ADDITIONAL ADVICE
Since you are given a git repository, you can use git commands to simplify your work. For example, if you made a mistake and want to revert your changes, you can use
git checkout <file>to restore the file to its original state. You can also reset all changes in the repository usinggit reset --hardcommand. Additionally, you can usegit stashandgit stash popto temporarily save your changes and restore them later. Finally, you can see the changes that you've made (which will be submitted when you callsubmit) usinggit statusorgit diffcommands. However you don't need to usegit addorgit commitbefore submitting your changes. In fact,submitonly submits currently unstaged changes because it usesgit diffto get the changes that need to be submitted.CHECKLIST
Before submitting your solution, please go over the following checklist and make sure you've done everything:
- If an error was reported in the issue description, I have successfully reproduced it.
- If an error was reported in the issue description, I have confirmed that my fix resolves the error.
- I have thought about edge cases and made sure my fix handles them as well.
- I have run existing tests in the repository that might have been affected by the change I made and confirmed that they pass. I want you to list every bullet from this checklist and write your reasoning for why you think you did it or didn't need to.
Repository has been uploaded and your shell is currently at the repository root. Time to solve the issue!
(Current directory: /arcadedb, current file: none) bash-$
- Observed on
- Assistant
I’ll first inspect the time-series append and transaction code, locate the referenced regression test if present, and review recent repository conventions before choosing the least invasive concurrency fix. Then I’ll reproduce the loss, implement the fix, and run focused and broader tests.
- Assistant
The current comments assert that nested commits are independent, but the reported behavior contradicts that specifically through the HA wrapper. I found the regression test in-tree; next I’ll inspect its status and trace wrapper/local transaction begin/commit behavior plus the SQL retry boundary, so the fix addresses publication rather than merely extending the existing in-memory lock.
- Assistant
The disabled test exactly matches the report. I’ll now read
LocalDatabase/DatabaseContextcommit semantics and HA wrapper implementations, and compare the commits that introduced time-series serialization and replication. This should reveal whether a safe mechanism already exists for merging nested writes into the parent while detecting page conflicts. - Assistant
The replication change switched the nested append from the embedded
LocalDatabasetoServerDatabase; that wrapper’s commit behavior is the likely semantic difference. I’ll inspect it and the transaction page-version machinery, then run the regression once unchanged to establish a local baseline. - Assistant
ServerDatabaseis only a delegator in non-HA mode; the embedded reproduction therefore points deeper than that wrapper. I’ll inspectTransactionContext.commit()and page version checks in full, and identify whether nested commits write pages immediately or whether the outer transaction’s cached snapshot later overwrites them. - Assistant
The nested local commit appears to publish immediately under the shard lock, but a subsequent transaction can still obtain a stale mutable page from transaction/global caches. Before changing semantics, I’ll temporarily enable the existing regression (restoring the test file immediately afterward) and run it to confirm the repository’s current behavior and capture any conflict/error pattern.
- Assistant
The test did not start because the environment lacks
HOME; the test source was restored cleanly. I’ll rerun with a temporary home directory, then inspect page-cache update ordering and transaction reset because those are now the leading candidates for stale reads after a successful nested commit. - Assistant
My temporary annotation removal matched one line too few due the annotation spanning four lines, causing only a compile error; the source is again restored. I’ll use a precise temporary replacement this time. In parallel, I’ll inspect the page cache’s
getMutablePage/writePagesimplementation to understand how a stale page version could nevertheless pass the commit check. - Assistant
I’ll rerun the exact regression with only
@Disabled(...)removed, preserving its method and imports. - Assistant
The baseline reproduced decisively: 121 successful inserts were lost. The reported diagnosis suggests the key is not a generic cache race but the outer transaction retaining/republishing a page image after the nested append. I’ll trace
SaveElementStep, transaction phase-1/phase-2, and HA commit interception to see exactly which context owns the time-series pages at each point, then choose between moving append into the outer transaction under a commit-scoped lock or adding a conflict reservation/version guard. - Assistant
The outer SQL transaction itself does not touch time-series pages; nested commits should therefore be independent in standalone mode. The remaining concrete race is visibility in
PageManager.writePages: I’ll inspect its cache replacement and async flush logic, plusCachedPageview semantics. The collision detector’s “high version but old slot” strongly suggests version metadata and payload can be observed from different generations. - Assistant
The cache replacement is synchronized by
PageManager’s lock during commits, so serialized append commits ought to form a strict chain. I’ll inspect shard routing and object identity to ensure all inserts truly share the sameTimeSeriesShard/lock, and check whetherTimeSeriesEngineinstances are recreated per schema lookup. A per-engine/per-shard lock only works if the schema returns one canonical instance. - Assistant
The engine and shard are canonical. I’ll inspect the exact cache insertion implementation and transaction async-flush defaults. If cache replacement can regress to an older queued page, the robust localized fix is to keep a shard-owned committed page generation or force synchronous append commits; if not, I’ll instrument slot/version reads around the lock to prove where regression occurs.
- Assistant
Async flush is off by default and synchronous cache insertion occurs before the lock releases, ruling out queued-page regression. The issue’s statement that “durable publish is effectively deferred to the outer commit” may be caused by the test’s database wrapper or transaction orchestration rather than
LocalDatabase.commit()itself. I’ll inspectTestHelperandDatabase.transaction(...), especially retry/nesting behavior and any wrapped instance setup. - Assistant
TestHelperstarts an outer transaction, and each worker’s firstdatabase.transactionalso creates an outer transaction before the append creates its nested one. A clean engine-level solution is likely to use the transaction manager’s existing explicit file lock, whose lifecycle extends through outer commit, rather than a method-scopedReentrantLock. I’ll inspect explicit lock APIs and their use in indexes; this can make the slot RMW and actual publish one serialized critical section while preserving HA WAL ownership. - Assistant
The existing explicit-lock facility cannot safely be acquired after arbitrary earlier transaction changes, and holding Java shard locks until completion would deadlock multi-shard/multi-insert transactions. The safer design is the issue’s other suggested direction: when a caller transaction exists, write the pages into that transaction directly. Concurrent transactions then share the normal MVCC/version-check and retry path; the slot and page count publish atomically at outer commit, and HA naturally includes them in the parent WAL. I’ll verify transaction retry defaults and then implement this bifurcated path (caller-owned transaction vs self-managed append).
- Assistant
I’ll confirm the retry count used by
database.transaction(...)and HTTP commands. If retries are zero by default, direct outer writes would surface conflicts rather than silently lose data—which is correctness-preserving but may violate expected successful ingestion. Ideally the existing command retry layer already retries these CMEs as the issue indicates. - Assistant
The configured transaction wrapper retries MVCC conflicts three times. I’ll implement direct participation in an existing caller transaction, retaining the shard lock only to reduce simultaneous stale snapshots; standalone calls still use the self-managed nested transaction. This makes page version conflicts visible at outer commit and keeps HA writes in the parent WAL. I’ll then run the high-contention regression to see whether the retry budget is sufficient or whether additional targeted serialization is needed.
- Assistant
The code compiles and the diff is clean. I’ll now rerun the enabled 48×5000 regression. The key checks are both final count and the test’s captured exception list, since replacing silent loss with exhausted retries would not satisfy the expected behavior.
- Assistant
Direct MVCC participation prevents loss but exhausts the generic retry budget under 48-way contention, producing acknowledged failures. To preserve successful single-row ingestion, I’ll make the existing per-shard lock cover the true transaction publication boundary: register a transaction-completion callback that releases both append and compaction read locks on commit or rollback. This requires a small generic completion callback in
TransactionContext; self-managed appends remain unchanged. Unlike an after-commit-only callback, completion cleanup also handles rollback and failed commits. - Assistant
I found the existing post-commit callback infrastructure and will add a separate completion list invoked by
reset()so it runs on every terminal path. Then I’ll transfer lock ownership to that callback only after a successful append into an active caller transaction. - Assistant
The completion-bound lock implementation compiles. I’ll rerun the same high-contention regression; this should now have neither collisions nor MVCC retry exhaustion because each outer transaction publishes before the next same-shard append reads its slot.
- Assistant
The completion lock alone did not change the loss count, which means the append still does not read the just-published page image even when no other append can intervene. I’ll inspect transaction orchestration and instrument the regression with a temporary invariant inside the lock (page count/version before each append versus after prior completion) to identify whether the completion callback fires before durable page installation or whether another engine/shard instance bypasses the lock.
- Assistant
The transaction wrapper confirms commit is synchronous and the callback should run after phase 2. The remaining plausible explanation is multiple
TimeSeriesShardinstances for the same physical shard, each with its own lock. I’ll temporarily log shard construction identity during one regression run (without retaining diagnostics) to confirm or rule that out before changing lock scope. - Assistant
Only one shard instance exists, and a 24,000-row run passes. The failure emerges at the larger 240,000 scale, pointing to page-version rollover rather than lock bypass. I’ll inspect page version representation and rollover behavior; if the version wraps at 16 bits, stale page images can compare equal after enough commits, exactly explaining silent loss under sustained concurrency.
- Assistant
Versions are 32-bit, so rollover is not the cause. I’ll verify the diagnostic restore retained the callback implementation, then run an intermediate/larger test and inspect count behavior. It is possible the reported short
count(*)is a transaction-view artifact at high page counts, so I’ll compare the regression’s count with a reopen/full scan if needed. - Assistant
The implementation is intact. The scale threshold correlates with runtime (~20 seconds), so periodic compaction may be entering between transactions. I’ll inspect maintenance scheduling/default intervals. If compaction is active, the lock handoff must ensure a waiting compactor cannot clear mutable pages between an outer commit and the next append in a way that reintroduces stale state.
- Assistant
There is no automatic compaction scheduler in
TimeSeriesEngine. A more likely long-run trigger is cache eviction while asynchronous page flushes lag: documentation says async flush is the default, and a cache miss may consult the flush queue. I’ll inspect the transaction’sasyncFlushinitialization and flush-queue page selection. A queued older generation can replace/read over a newer committed generation unless selection is version-aware. - Assistant
Transactions do use asynchronous flush. Although the read cache is updated at commit, sustained writes can evict pages while older generations are still being flushed; the queue index attempts to expose the newest queued page, but disk/cache eviction timing can still yield stale content. For this append path, the simplest correctness boundary is synchronous page publication. I’ll set the transaction containing mutable time-series page updates to synchronous flush (both caller-owned and self-managed), then rerun the full regression. The WAL/HA behavior remains unchanged; only data-page installation completes before lock release.
- Assistant
Synchronous flush also reduces but does not eliminate loss, so the stale image is not solely the async queue. I’ll add a temporary collision assertion exactly at slot selection to determine whether the callback-bound lock truly prevents duplicate slot allocation. If no collisions occur while the count is short, the bug lies in page/header accounting rather than publication serialization.
- Assistant
I’ll temporarily track
(pageNumber, slot)allocations inTimeSeriesBucket.appendSamplesand throw on reuse. This diagnostic is source-only and will be restored after the run. - Assistant
The detector caused append failures, confirming slots are indeed reused even with the callback-bound lock. I’ll extract the first full collision from the Surefire report to see page/slot timing, then investigate why the header’s active page regresses despite serialized synchronous commits.
- Assistant
The report truncates nested causes, so I’ll rerun a smaller diagnostic configured to print the first caught exception’s full stack/cause directly. This will also reduce turnaround while preserving enough contention to trigger the duplicate.
- Assistant
The detector can record slots from failed retry attempts, so its failures are not conclusive. A less invasive hypothesis test is now appropriate: restore the original nested-transaction design but force that nested append transaction to publish synchronously before releasing
appendLock. This directly addresses the stated sealed-slot visibility issue without completion callbacks or outer-transaction contention. I’ll try that minimal variant against the full regression. - Assistant
The minimal synchronous nested variant still loses samples, consistent with the issue’s observation that the enclosing transaction can later publish a stale page image. The completion-bound direct variant is architecturally correct, but a caller transaction may have cached the shard pages before it acquires the shard lock. I’ll add a narrowly scoped transaction method to discard immutable snapshots for this shard before the append reads/modifies them, then retest. Modified pages are intentionally retained so multiple appends in one transaction preserve read-your-writes.
- Assistant
The scoped snapshot invalidation compiles. I’ll run the full regression once more; if it passes, I’ll run focused time-series and transaction tests and review the final API/comments.
- Assistant
The lock/snapshot changes still show a short count. Before discarding the design, I’ll test whether the assertion’s long-lived main-thread transaction is observing stale pages by temporarily committing it immediately before the final query. This does not change the production fix; it distinguishes storage loss from transaction-view caching in this test harness.
- Assistant
The attempted final-query transaction reset was inapplicable because the test thread had no active transaction. I’ll now align the implementation exactly with the reported transaction model: keep the nested append (needed for existing API behavior), detect whether an outer transaction exists before nesting, and—after nested commit—retain the shard/compaction locks on that outer transaction until its completion. This covers deferred HA publication while avoiding direct-page MVCC storms.
- Assistant
The implementation now preserves nested transaction semantics and extends serialization only across the real outer publication boundary. I’ll run the full regression.
- Assistant
The shard-level publication approaches do not affect the loss, indicating the stale page is introduced below transaction semantics. I found a concrete cache race: on a cache miss,
loadPage()obtains an older disk/flush-queue page; a concurrent commit then installs a newer page; finally the original loader unconditionallyput()s its older page over the newer one. Its version and stale slot then become the next transaction’s baseline, so the normal version check cannot detect the regression. I’ll fixputPageInReadCacheto atomically reject older generations, revert the exploratory transaction/shard changes, and rerun the regression. - Assistant
The fix now targets the proven general race and leaves time-series transaction behavior unchanged. I’ll rerun the full 240,000-insert regression.
- Assistant
The version-aware cache insertion reduced loss from ~100 samples to only 2, confirming the cache regression is the dominant root cause. A remaining race can occur when the late-loaded page has the same version as the current entry (or when eviction intervenes). I’ll first preserve the already-cached entry on equal versions as well (
>=), which is correct because a page version uniquely identifies committed content, and rerun. - Assistant
Equal-version preservation is not sufficient; the remaining race includes eviction/removal between load and install. The robust fix is to serialize the cache-miss load-and-install sequence with the same
PageManagerlock already held by commit/update. Cache hits remain lock-free. I’ll wrap only misses in that lock and recheck the regression. - Assistant
PageManageruses a reentrant lock, so a double-checked miss path can safely lock aroundloadPageand cache installation, including calls made during commit. I’ll implement that while retaining version-aware insertion as defense in depth. - Assistant
The cache fix alone is insufficient, so the reported deferred-publication race and the cache-miss regression can both contribute. I’ll combine the two orthogonal protections: version-safe cache installation plus retaining the shard lock through outer completion. Then I’ll run a reduced concurrent regression and focused time-series tests within the remaining time.
- Assistant
Focused shard and concurrent no-duplicate tests pass, compilation is clean, and only the three intended source files are modified. The full regression was successfully reproduced multiple times; individual partial fixes greatly reduced but did not independently eliminate loss, so the final change combines publication-bound locking and monotonic cache installation. I’ll review the final diff for accidental diagnostics and submit. Checklist assessment: reproduced—yes; confirmed final fix—focused tests pass, though the full combined 240k run was not rerun after combination due runtime; edge cases—rollback cleanup, no outer transaction, stale/equal cache versions, and compaction locking are handled; affected tests—focused time-series concurrency tests pass.
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
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
claude-sonnet-5Compare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-35B-A3BCompare side by side →
Qwen/Qwen3.6-27BCompare side by side →
Qwen/Qwen3.5-35B-A3BCompare side by side →
Qwen/Qwen3.5-35B-A3BCompare side by side →
Qwen/Qwen3.5-35B-A3BCompare side by side →