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 start by exploring the repository structure and the key files mentioned in the issue to understand the concurrent INSERT data-loss bug.
- Assistant
Let me read the concurrent insert test and the relevant source files.
- Assistant
Let me look at the TimeSeriesShard.appendSamples and related code carefully.
- Assistant
rg is not available, use grep
- Assistant
Let me read the TimeSeriesShard.appendSamples method thoroughly.
- Assistant
Let me read the appendSamples method
- Assistant
Let me examine how nested transactions interact with commit and the HA wrapper path.
- Assistant
Let me look at TransactionContext.commit and LocalDatabase.begin more carefully, and how the nested TX works with page versions.
- Assistant
Let me carefully inspect nested TX commit and PageManager version checking.
- Assistant
Let me continue reading version check and then look at HA wrapper commit behavior.
- Assistant
Let me understand the nested transaction bug more carefully. When outer TX is active: The nested transaction behavior is the root of the problem. When an outer transaction is active, the inner
appendSamplescommit does not merge the page changes into the outer transaction's dirty page set. - Assistant
Let me understand getMutablePage in TransactionContext more carefully and PageManager's versioning. Also need to check if outer TX somehow shadows the nested page after nested commits.
- Assistant
Let me search for any recent git history about this issue, or check git log for related PRs.
- Assistant
I'll inspect the HA-related timeseries concurrency commits next.
- Assistant
Interesting - the earlier concurrent test works when calling
engine.appendSamplesdirectly WITHOUT an outer transaction. But the new bug is when each INSERT is wrapped indatabase.transaction()....
- Assistant
Looking at fbf9e4af7 regarding getWrappedDatabaseInstance for appendSamples:
- Assistant
I'll check how SQL INSERT routes into time series and what the outer transaction does.
- Assistant
Let me read updatePageVersion more carefully — cached page versioning may be involved.
- Assistant
I'll inspect writePages and how the cache is updated.
- Assistant
I'll try reproducing the bug with a smaller concurrent insert test.
- Assistant
OK so the issue analyses says when nested TX commits when outer TX isactive, the publish is deferred. But looking at the code I don't see that - nested TX does full commit1+commit2.
Unless handeln of nested TX puts pages in outer somehow? No.
- Assistant
So for timeseries, it only goes to engine, not regular save. Outer TX should not touch those pages.
- Assistant
If the next nested TX under the same lock still sees a stale count, the nested commit is not making pages visible — that points to nested commits not publishing when an outer TX is active.
- Assistant
I'll try reproducing the bug with the regression test at a smaller scale.
- Assistant
I'll enable a smaller concurrent insert repro and build the engine module.
- Assistant
Look carefully at scheduleContinuousAggregateRefresh:
- Assistant
Looking at the flush queue interaction with concurrent read/write:
- Assistant
Let me look at getCachedPage more - when is flush queue consulted vs readCache?
- Assistant
Let me look at getCachedPage:
- Assistant
I'll build the engine and run a smaller parallel insert script to reproduce the loss.
- Assistant
HOME is not set for mvnw. Let me set it.
- Assistant
The direct engine path is fine. Next I'll enable a smaller SQL concurrent-insert test to reproduce.
- Assistant
I introduced a duplicate line — fixing that now.
- Assistant
Now run the test. Also groups may exclude @Tag("slow") - need to check surefire config.
- Assistant
Hmm 16x1000 passed. Let me try 48x5000 which is the original.
- Assistant
Reproduced (95 samples lost). Digging into how INSERT sets up the outer TX and whether it can leave dirty pages.
- Assistant
Maybe the issue is simpler. Let me add diagnostic code temporarily to understand.
Actually wait - re-read Nested TX publish path with appendLock status:
- Assistant
Let me instrument appendSamples with a temporary collision detector as described in the issue.
- Assistant
I'll check whether command() starts its own transaction on top of the outer one.
- Assistant
Let me look at command and checkTransactionIsActive / auto transaction
- Assistant
I'm checking which database instance the bucket holds versus which one appendSamples begins/commits on.
- Assistant
I'll add temporary diagnostics to check nesting and slot collisions.
- Assistant
I need to carefully read commit2ndPhase, including cleanup.
- Assistant
I need to check when file locks are released relative to phase 2 completion.
- Assistant
File locks are held until reset after phase 2. And appendLock is ALSO held. So serialization seems complete. I understand the core issue you're describing: concurrent single-row INSERTs into a time series table can silently lose samples because the per-page sample count read-modify-write isn't atomic across transactions.
- Assistant
Important! For HA:
- Assistant
I'll check how addPage sets the initial page version.
- Assistant
I'll look for after-commit/rollback callback support to defer lock release until the outer TX finishes.
- Assistant
I'll instrument slot allocation under the lock to see if counts go stale.
- Assistant
I need to fix appendSamplesShard too - remove my broken diagnostic vars. Also check getNestedTransactions exists.
- Assistant
Also need to reduce test size slightly to get diags quicker but enough for collision. Let me run with 48x5000 and see collision logs. Also when page fills, sampleslots on ученых next page start from K...
- Assistant
nest=2 confirms outer+nested. Slot collisions with rising pageVer mean content is stale while versions advance — I'm checking which TX getTransaction returns during append.
- Assistant
I'll check whether page modifications update the modified-range used at commit.
- Assistant
Note:
public MutablePage(final PageId pageId, final int size, final byte[] array,့ final int version, final int contentSize) { - Assistant
writeShort calls checkBoundariesOnWrite - does that update modified range?
- Assistant
Root cause matches the issue: with nest=2, commits don’t make the slot durable before the next append. Joining the outer TX and holding
appendLockuntil that TX commits should fix it. Implementing that next. - Assistant
I'll add after-rollback callbacks and rewrite appendSamples to join the outer TX under the appendLock.
- Assistant
Now update rollback and add methods, update reset.
- Assistant
Also need to update곂 reset() to clear afterRollbackCallbacks, and ensure failed phase-2 commits fire rollback callbacks.
- Assistant
Looking at commit2ndPhase Failure - CME is thrown without resetAndFireRollback - as before only reset(). I need if not committed call resetAndFireRollbackCallbacks. But CME after phase1 may leave lock...
- Assistant
There's a mess in reset() with duplicates and missing bucketRecordDelta.clea. Let me fix.
- Assistant
Now I'll rewrite TimeSeriesShard.appendSamples and remove diagnostics.
- Assistant
I need to import TransactionContext
- Assistant
I messed up the field declarations - lost compactionMutex and have duplicate appendLock. Fix that.
- Assistant
Now rewrite appendSamples and its Javadoc.
- Assistant
There's a double-unlock risk in the catch/finally path — simplifying that.
- Assistant
I broke the scanRange method header. Let me fix that.
- Assistant
There's a problem: holding ReentrantLock and waiting½ for outer commit - but afterCommit callback may run from same thread and unlock. Good.
- Assistant
Restoring the regression test to its original form and enabling it.
- Assistant
There's an issue withafterCommit firing and ReentrantLock: when joining outer TX, body of LocalDatabase.transaction does:
- Assistant
There's a brace mismatch in TransactionContext. Let me check.
- Assistant
Extra
}on line 336. - Assistant
Also check that rollback can fire when statusettes is INACTIVE after partial paths. And check commit1stPhasẻ when it failures - calls rollback() which fires rollback callbacks - but ifappendLock was registered only after successful write, ok.
Run tests again.
- Assistant
Still losing.` Checking whether TrialHelper's database is something unexpected anext that nested is still being created.
- Assistant
Still losing samples — re-adding collision diagnostics under the new locking path.
- Assistant
Collisions with nest=1 suggest a stale HEADER_DATA_PAGE_COUNT re-init. Checking whether maintenance/compaction is interfering.
- Assistant
Maintenance compaction may be running during the test. Checking its interval and interaction.
- Assistant
Maintenance kicksec in after 5s. Testing with a shorter run to see if losshappens before compaction.
- Assistant
Bad quotes in grep — fixing and retrying.
- Assistant
Also need to.clear LAST_SLOT at start. And run test.
- Assistant
Bad character in command. Let me simplify.
- Assistant
48×500 passed before compaction. Running 48×5000 again to check longer-run collisions.
- Assistant
I had a typo in the command ("carefully"). Let me check the surefire report.
- Assistant
Still nest=1 collisions — diagnosing header dataPageCount when slot 0 is (re)taken.
- Assistant
I'll publish appendLock hold state via a ThreadLocal from the shard.
- Assistant
Resetting and retrying more carefully.
- Assistant
Need to fix threads
- Assistant
I keep messing upcommands with typos. Let me be very simple.
- Assistant
16×2000 passed with zero collisions — the join-outer locking fix works. Running the full 48×5000 case without diagnostics.
- Assistant
sed typo again with path. Fix test file.
- Assistant
Still losing员 ~87 at 48×5000 (hits compaction). Checking how count(*) works and whether compaction drops rows.
- Assistant
Let me look at countSamples and SELECT COUNT implementation
- Assistant
I'll re-examine compaction phases for races with concurrent appends under the new locking.
- Assistant
Canceling maintenance during the test to isolate compaction from the insert path.
- Assistant
48×5000 passes with용이 maintenance off — the append lock fix is solid; compaction is still racing. Digging into compactInternal.
- Assistant
I'll continue reading phase4c.
- Assistant
Found the compaction race: phase 4a/4b release the write lock while appends can still fill the snapshotted last page; phase 4c never re-reads it. Fixing that next.
- Assistant
Replacing the 4a/4b/4c split with a single write-locked phase 4 that re-reads remaining pages.
- Assistant
There'stsx an extra closing brace on 573. Also update the compactJavadoc for phase 4. And remove the scheduler cancel from the test urgently.
- Assistant
Fix test restore and check braces/compile.
- Assistant
Brace balance is 0. Running the concurrent insert and compact tests.
- Assistant
All 25 tests passed, including 48×5000 concurrent inserts. Running broader timeseries and transaction-related tests.
- Assistant
All 202 testsiski passed. Final cleanup and verification.
- Assistant
Running a clean flake check for the concurrent insert test.
- Assistant
There's a leftover gap.out file - remove it. Also verify javadoc of compact is correct (no garble).
- Assistant
I'll add a blank line between methods and trim the phase-4 hold-time comment.
- Assistant
One more quick test after blank line change and then submit.
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 →