Concurrent writers in SQLite with C# (BEGIN CONCURRENT) · onelite cookbook

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):

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.

Terminal

dotnet new console -o ConcurrentWriters && cd ConcurrentWriters
dotnet add package RecursiveAI.OneLite --version 0.6.0-preview.2

Put this in Program.cs:

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<bool, double>();
    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<long>($"select max(id) from orders");
 
var aInserted = new TaskCompletionSource();
var bCommitted = new TaskCompletionSource();
Task aDone = a.WriteAsync(async tx =>
{
    long id = await tx.ScalarAsync<long>(
        $"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<long>(
    $"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<long>(
        $"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:

Terminal

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.

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:

Terminal

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, writer processes killed with SIGKILL, and writers beside a sqlite3 process (see “How it is checked” in docs/concurrency.md).
A preview: APIs may change between previews.

BEGIN CONCURRENT docs · The happy path · Transactions · All recipes