--- title: "Concurrent writers in SQLite with C# (BEGIN CONCURRENT)" url: https://recursiveai.org/onelite/cookbook/concurrent-writers-sqlite-csharp/ description: "Several writers on one SQLite file at once in C#, with OneLite’s BEGIN CONCURRENT: a complete program that times it against BEGIN IMMEDIATE, shows a WHERE id > last poller skipping a row, checks the file with sqlite3, and states the limits." version: "0.6.0-preview.2" --- # Concurrent writers in SQLite with C# (BEGIN CONCURRENT) SQLite lets one transaction write at a time. OneLite adds opt-in `BEGIN CONCURRENT`: in its changelog’s words, “row-level concurrent writers on a standard WAL file. Serializable by default”. Transactions on different connections run side by side and conflict only when they touch the same rows. It is off by default. Turn it on with `Database.HappyPathOptions() with { ConcurrentWrites = true }`, and `WriteAsync` runs each delegate as `BEGIN CONCURRENT`. Know its limits before you rely on it (all from [docs/concurrency.md](https://recursiveai.org/onelite/docs/concurrency/)): - **Rowids are not in commit order.** “A poller that remembers the largest id it saw and asks `WHERE id > :last` can skip a row that commits later with a smaller id. Poll with a commit-ordered column or sequence of your own instead.” The program below shows it happen. - **Short transactions are slower.** “With no work in the body (B = 0) concurrent is 7% to 19% slower: every commit that follows another also replays its trace.” - **No gain with `synchronous=FULL` yet**: “the write lock is held through the sync, so both modes commit at one sync per commit” (“group commit is not implemented”). FULL is what `Database.OpenAsync` uses unless you pass options; this program sets NORMAL. - **A 5-second commit hold limit.** `ConcurrentCommitHoldLimitMs` (“default 5000”): a commit whose replay is still running that long “stops, releases the lock and fails with `SQLITE_BUSY_TIMEOUT` (extended code 773)”, and `WriteAsync` runs the delegate again. ```shell dotnet new console -o ConcurrentWriters && cd ConcurrentWriters dotnet add package RecursiveAI.OneLite --version 0.6.0-preview.2 ``` Put this in `Program.cs`: ```csharp using System.Diagnostics; using RecursiveAI.OneLite; using RecursiveAI.OneLite.Storage.Pager; const int Writers = 8, PerWriter = 250; // The happy-path options (WAL, which BEGIN CONCURRENT needs), with two changes. DatabaseOptions Options(bool concurrent) => Database.HappyPathOptions() with { Synchronous = SynchronousMode.Normal, // with FULL there is no gain yet ConcurrentWrites = concurrent, // on: WriteAsync runs BEGIN CONCURRENT }; // 1. One workload, both ways: 8 writers, each with its own Database (connection), // each transaction one insert plus some application work. foreach (int workUs in new[] { 0, 200 }) { var rate = new Dictionary(); foreach (bool concurrent in new[] { false, true }) { string path = $"orders-{workUs}-{(concurrent ? "concurrent" : "immediate")}.db"; await using (var setup = await Database.OpenAsync(path, Options(concurrent))) await setup.ExecuteAsync( $"create table orders (id integer primary key, writer int, item text)"); var writers = new Database[Writers]; for (int w = 0; w < Writers; w++) writers[w] = await Database.OpenAsync(path, Options(concurrent)); var clock = Stopwatch.StartNew(); await Task.WhenAll(writers.Select((db, w) => Task.Run(async () => { for (int i = 0; i < PerWriter; i++) await db.WriteAsync(async tx => { await tx.ExecuteAsync( $"insert into orders (writer, item) values ({w}, {$"item {i}"})"); Work(workUs); }); }))); clock.Stop(); foreach (var db in writers) await db.DisposeAsync(); rate[concurrent] = Writers * PerWriter / clock.Elapsed.TotalSeconds; } Console.WriteLine($"work {workUs,3} µs: IMMEDIATE {rate[false],6:N0}/s, " + $"CONCURRENT {rate[true],6:N0}/s, ratio {rate[true] / rate[false]:0.00}"); } // 2. Rowids are not in commit order: A takes an id, B takes a larger one and commits first. const string DbFile = "orders-200-concurrent.db"; await using var a = await Database.OpenAsync(DbFile, Options(true)); await using var b = await Database.OpenAsync(DbFile, Options(true)); await using var reader = await Database.OpenAsync(DbFile, Options(true)); long last = await reader.ScalarAsync($"select max(id) from orders"); var aInserted = new TaskCompletionSource(); var bCommitted = new TaskCompletionSource(); Task aDone = a.WriteAsync(async tx => { long id = await tx.ScalarAsync( $"insert into orders (writer, item) values (100, 'A') returning id"); Console.WriteLine($"A inserted id {id}, not committed yet"); aInserted.TrySetResult(); await bCommitted.Task; // hold A open until B has committed }); await aInserted.Task; long bId = await b.WriteAsync(async tx => await tx.ScalarAsync( $"insert into orders (writer, item) values (101, 'B') returning id")); Console.WriteLine($"B inserted id {bId} and committed"); await Poll(); bCommitted.SetResult(); await aDone; Console.WriteLine("A committed"); await Poll(); async Task Poll() // the usual "new rows since last time" poller { var ids = await reader.QueryAsync( $"select id from orders where id > {last} order by id"); Console.WriteLine($"poller, where id > {last}: [{string.Join(", ", ids)}]"); if (ids.Count > 0) last = ids[^1]; } static void Work(int us) // stands in for application work inside the transaction { long start = Stopwatch.GetTimestamp(); while (Stopwatch.GetElapsedTime(start).TotalMicroseconds < us) { } } ``` Run it in Release: ```shell dotnet run -c Release ``` ```text work 0 µs: IMMEDIATE 6,426/s, CONCURRENT 8,597/s, ratio 1.34 work 200 µs: IMMEDIATE 2,796/s, CONCURRENT 4,696/s, ratio 1.68 A inserted id 2001, not committed yet B inserted id 2002 and committed poller, where id > 2000: [2002] A committed poller, where id > 2002: [] ``` **The timings.** Each line runs 8 writers × 250 transactions, first with `BEGIN IMMEDIATE` (`ConcurrentWrites` off: one writer at a time) and then with `BEGIN CONCURRENT`; the ratio is `BEGIN CONCURRENT` commits per second divided by `BEGIN IMMEDIATE` commits per second. With 200 µs of work, the writers overlap it. These numbers are from one machine and vary from run to run: a 16-core AMD Ryzen 9 7950X3D on Ubuntu 26.04, .NET 10, the database on tmpfs (memory, so syncs cost nothing), with other jobs running (load average 5 to 6.6). Across this run and four more runs of the same program, the ratio was 0.99 to 1.34 with no work and 1.68 to 3.63 at 200 µs. With no work inside the transaction there is nothing to overlap, and the docs measure it 7% to 19% slower (limits above); this machine did not show that slowdown. The project’s own indicative numbers, on local NVMe, are in [docs/concurrency.md](https://recursiveai.org/onelite/docs/concurrency/#when-it-helps). **The poller.** A inserts first and gets the smaller id; B gets the next one and commits first. The poller sees B, remembers its id, and asks only for larger ids from then on, so when A commits its row is never seen, although it is in the table. With `BEGIN IMMEDIATE`, A would hold the write lock and B would wait, so ids would follow commit order, as in SQLite. - One `Database` serializes its own calls, so concurrency needs one `Database` (connection) per writer, as here, or several processes. - `WriteAsync` runs the delegate again when its commit can’t be merged (`SQLITE_BUSY_SNAPSHOT`), so keep it to database work. - In SQL, the statements are `BEGIN CONCURRENT` (serializable) and `BEGIN CONCURRENT SNAPSHOT` (snapshot isolation), then `COMMIT`. ## Check it with sqlite3 The file is a standard SQLite database in WAL mode. The `sqlite3` shell (3.53.4, from sqlite.org) checks it and shows A’s smaller id, committed after B’s: ```shell sqlite3 orders-200-concurrent.db "pragma integrity_check" "pragma journal_mode" \ "select count(*) from orders" "select id, item from orders where writer >= 100" ``` ```text ok wal 2002 2001|A 2002|B ``` ## Poll in commit order instead The docs’ advice is to poll “with a commit-ordered column or sequence of your own”. If you number rows yourself from a counter row that every transaction updates, keep in mind that two transactions that change the same row don’t both commit: in the docs’ words, “two that change the same row, or where one read a row the other changed, do not”. The loser’s delegate runs again, so writers on one counter commit one at a time. ## Limits - The four above: rowids out of commit order, short transactions slower, no gain with `synchronous=FULL`, and the 5-second commit hold limit. - Not merged: in the docs’ words, “schema changes (DDL), header pragmas, `VACUUM`”, “writes to a virtual table” and “a trace longer than 16 million entries” make a transaction fail with `SQLITE_BUSY_SNAPSHOT` if anyone else committed since it began. “Writing to an attached database inside the transaction is `SQLITE_ERROR`.” - OneLite only: `sqlite3` reports a syntax error for `BEGIN CONCURRENT`, though it can open the file and write with a plain `BEGIN` beside OneLite’s writers. `Database.Open` and the ADO.NET provider don’t use it either. - Checked by the project’s own testing: unit tests, a crash sweep, the [history checker](https://recursiveai.org/onelite/docs/history-checker/), writer processes killed with SIGKILL, and writers beside a `sqlite3` process (see “How it is checked” in [docs/concurrency.md](https://recursiveai.org/onelite/docs/concurrency/#how-it-is-checked)). - A preview: APIs may change between previews. [BEGIN CONCURRENT docs](https://recursiveai.org/onelite/docs/concurrency/) · [The happy path](https://recursiveai.org/onelite/docs/happy-path/) · [Transactions](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/) · [All recipes](https://recursiveai.org/onelite/cookbook/)