Two concurrent transactions both commit, and one of them is silently lost: no write-write conflict detection #304

Two explicit transactions that are open at the same time and write to the same collection both commit without error, and the changes of the one that committed first are gone. The document is not in the collection, not after a reopen, and nothing reports a conflict. This is the shape the README describes for sessions: "Multiple sessions on the same engine run independent concurrent transactions", and line 36: "Full ACID transactions with Write-Ahead Logging (WAL) and Snapshot Isolation".

Found while reviewing the storage layer for #296.

Repro

// typed
var t1 = db.BeginTransaction();
var t2 = db.BeginTransaction();
var a = new Person { Name = "from-t1" }; var b = new Person { Name = "from-t2" };
await db.People.InsertAsync(a, t1);
await db.People.InsertAsync(b, t2);
await t1.CommitAsync();
await t2.CommitAsync();
// count = 2 (expected 3, one seed document); a is not found, b is; same after reopen

// sessions
using var s1 = engine.OpenSession(); using var s2 = engine.OpenSession();
using var tx1 = s1.BeginTransaction(); using var tx2 = s2.BeginTransaction();
var id1 = await s1.InsertAsync("orders", doc1);
var id2 = await s2.InsertAsync("orders", doc2);
await s1.CommitAsync();
await s2.CommitAsync();
// count = 2 (expected 3); id1 is not found; same after reopen

The same happens with BLiteEngine.BeginTransaction() and DynamicCollection.InsertAsync(doc, tx). In every run the first committer loses.

Expected: both inserts are in the collection, or the second CommitAsync fails with a conflict and the caller can retry.

Actual: both commits succeed and the first one's writes are overwritten.

Mechanism (0450b97)

• Each transaction keeps private full-page copies of every page it writes (StorageEngine.WritePage, src/BLite.Core/Storage/StorageEngine.Pages.cs:107-127: txnPages[pageId] = data.ToArray()).
• The collection write lock is held only for the duration of InsertAsync, not until the caller commits (src/BLite.Core/Collections/DocumentCollection.cs:2273-2306). So the second transaction reads the committed page (without the first transaction's uncommitted slot), inserts into its own copy of the same data page and the same primary-index leaf, and records that copy.
• Commit publishes each page image wholesale, last writer wins (src/BLite.Core/Storage/StorageEngine.GroupCommit.cs:211-212: _walIndex[kvp.Key] = kvp.Value). The second commit replaces the data page and the leaf that held the first transaction's document and index entry.
• IsolationLevel is stored on the transaction (src/BLite.Core/Transactions/Transaction.cs:16, 36, 48) and never read.
• The code knows: src/BLite.Core/Indexing/FullTextIndex.cs:25-27 — "two explicit transactions writing to the same collection concurrently lose one of the updates (BLite has no write-write conflict detection between transactions)".

The same applies to updates and deletes that touch the same page, and to any shared page such as an index leaf, so two transactions on different documents of one collection are enough.

Affected versions: since per-transaction page caches were introduced (8a1c149, v1.0.0) through v5.2.3.

Fix considerations

• Minimum (5.x): detect the conflict at commit. Record, per page a transaction writes, the version (commit sequence) it read; at commit, if any of those pages was published by another commit since, fail with a conflict exception and roll back. First committer wins; the loser can retry. That makes the README's isolation claim true for writes without changing the file format.
• Alternative (5.x): hold the collection write lock from a transaction's first write until its commit or rollback. Simple and safe, but it serialises explicit transactions per collection (per database in single-file mode, where the lock is shared) and turns long transactions into lock timeouts.
• v6: page-level conflict detection plus snapshot publication as part of the transaction manager (Epic: v6.0.0 — migration guide, changelog, docs, samples and release engineering #296).
• Docs: until a fix lands, the README should not claim snapshot isolation, and the session section should say that concurrent explicit transactions on one collection are not safe.
• Test: a two-session model in the store-model oracle of test: oracle tests for the LINQ provider and the typed collection store #250 (two transactions interleaved, both commit), expecting either both writes or a conflict error, never a lost write.

Related: #291 (two browser tabs lose a committed write, same symptom across tabs), #20 (optimistic concurrency at document level), #296.