Using CSharpDB from Multiple Threads

CSharpDB works well in multi-threaded applications, but the right pattern depends on which API you are using. The engine is designed around a simple rule: one writer at a time, many readers at once.

That means you do not need to build your own lock manager around the database. You do need to choose the right ownership model for reads, writes, and explicit transactions.

The Concurrency Model

At the engine level, CSharpDB uses a single-writer, multiple-reader model with snapshot isolation:

• Only one write transaction is active at a time. Contending writes serialize behind the writer lock.
• Readers see a stable snapshot. A reader does not block the writer, and the writer does not rewrite the reader's view underneath it.
• Auto-commit writes are the easiest way to scale in-process write work. Independent tasks can issue writes concurrently and let the engine serialize commit order.

Since v2.9.0, CSharpDB also ships initial multi-writer support: explicit WriteTransaction with conflict-detected retry, shared auto-commit non-insert isolation, and opt-in ConcurrentWriteTransactions for shared implicit inserts. The physical WAL commit path is still serialized at the storage boundary, but the engine can now overlap write preparation across multiple transactions.

In one process, the recommended topology is a single long-lived Database instance for writes plus ReaderSession instances for concurrent snapshot reads.

Recommended Pattern: One Shared Database

If you are using the direct engine API, prefer one warm Database instance shared by your application service or host. Route concurrent work through that owner instead of opening the same file multiple times in the same process.

using CSharpDB.Engine;

await using var db = await Database.OpenAsync("app.db");

await db.ExecuteAsync("""
    CREATE TABLE IF NOT EXISTS jobs (
        id      INTEGER PRIMARY KEY,
        worker  INTEGER NOT NULL,
        payload TEXT NOT NULL
    )
    """);

Task[] writers = Enumerable.Range(0, 4)
    .Select(workerId => Task.Run(async () =>
    {
        for (int i = 0; i < 100; i++)
        {
            int id = workerId * 1000 + i;
            await db.ExecuteAsync(
                $"INSERT INTO jobs VALUES ({id}, {workerId}, 'item-{id}')");
        }
    }))
    .ToArray();

await Task.WhenAll(writers);

This is the simplest and most efficient in-process write pattern. Each task can submit work independently, and the engine coordinates the actual write transaction lifecycle.

Snapshot Reads: One ReaderSession Per Concurrent Reader

For concurrent reads, create a separate ReaderSession for each live reader. Each session sees a point-in-time snapshot and can run while writes continue in the main database.

Task[] readers = Enumerable.Range(0, 8)
    .Select(_ => Task.Run(async () =>
    {
        using var reader = db.CreateReaderSession();
        await using var result = await reader.ExecuteReadAsync(
            "SELECT COUNT(*) FROM jobs");

        var rows = await result.ToListAsync();
        Console.WriteLine(rows[0][0].AsInteger);
    }))
    .ToArray();

await Task.WhenAll(readers);

Do not share one ReaderSession across multiple concurrent queries. A reader session is reusable for sequential reads, but it only allows one active query result at a time.

Concurrent Writes: Let Auto-Commit Serialize

When each write is logically independent, let each task issue its own auto-commit statement. That is usually better than wrapping all concurrent work in one big shared explicit transaction.

Task[] writers = Enumerable.Range(0, 4)
    .Select(workerId => Task.Run(async () =>
    {
        for (int i = 0; i < 50; i++)
        {
            int id = workerId * 100 + i;
            await db.ExecuteAsync(
                $"INSERT INTO jobs VALUES ({id}, {workerId}, 'payload-{id}')");
        }
    }))
    .ToArray();

Task[] readers = Enumerable.Range(0, 2)
    .Select(_ => Task.Run(async () =>
    {
        using var reader = db.CreateReaderSession();
        await using var result = await reader.ExecuteReadAsync(
            "SELECT COUNT(*) FROM jobs");
        var rows = await result.ToListAsync();
        Console.WriteLine(rows[0][0].AsInteger);
    }))
    .ToArray();

await Task.WhenAll(writers.Concat(readers));

Readers may observe an older snapshot while writers continue to commit. That is expected. Snapshot isolation favors consistency per reader rather than forcing all readers to chase the newest row count mid-query.

Explicit Transactions: Keep Them on One Task

Explicit transactions are still useful when several statements must succeed or fail together. The important rule is ownership: one task should own the entire explicit transaction from begin to commit or rollback.

await Task.Run(async () =>
{
    await db.BeginTransactionAsync();
    try
    {
        await db.ExecuteAsync(
            "INSERT INTO jobs VALUES (9001, 99, 'step-a')");
        await db.ExecuteAsync(
            "INSERT INTO jobs VALUES (9002, 99, 'step-b')");

        await db.CommitAsync();
    }
    catch
    {
        await db.RollbackAsync();
        throw;
    }
});

Do not have several unrelated threads participate in the same explicit transaction. Transaction state lives on the shared Database instance, so spreading one transaction across multiple concurrent callers makes coordination harder, not easier.

ADO.NET Pattern: One Connection Per Worker

If you are using CSharpDbConnection and CSharpDbCommand, the safer pattern changes slightly: use one connection per worker or request, and enable pooling when opens are frequent.

using CSharpDB.Data;

string cs = "Data Source=app.db;Pooling=true;Max Pool Size=16";

await Parallel.ForEachAsync(Enumerable.Range(0, 100), async (i, ct) =>
{
    await using var conn = new CSharpDbConnection(cs);
    await conn.OpenAsync(ct);

    await using var cmd = conn.CreateCommand();
    cmd.CommandText =
        "INSERT INTO jobs VALUES (@id, @worker, @payload)";
    cmd.Parameters.AddWithValue("@id", i);
    cmd.Parameters.AddWithValue("@worker", 1);
    cmd.Parameters.AddWithValue("@payload", $"item-{i}");

    await cmd.ExecuteNonQueryAsync(ct);
});

Avoid sharing the same CSharpDbConnection, CSharpDbCommand, or active transaction object across concurrent threads. Connection state and transaction ownership are per connection.

Shared In-Memory Connections

The ADO.NET provider also supports named shared in-memory databases:

Data Source=:memory:shared-cache

Multiple live connections can attach to the same named in-memory host. One connection may own an explicit transaction at a time, while other connections continue reading from the last committed snapshot. That makes shared in-memory useful for tests, local caches, and host-owned transient databases.

Common Mistakes to Avoid

• Do not open the same database file twice in one process just to split reads and writes. Prefer one shared Database instance and create ReaderSession objects from it.
• Do not share one ReaderSession across concurrent queries. Create one reader session per live reader.
• Do not spread one explicit transaction across many threads. Keep transaction ownership on one task.
• Do not share one ADO.NET connection across concurrent workers. Use one connection per worker and enable pooling.
• Do not treat snapshot reads as stale bugs. A snapshot reader is intentionally isolated from later commits.

Which Pattern Should You Choose?

Use the direct Database API when you want one in-process owner, very fast reads, and simple control over snapshot sessions. Use CSharpDbConnection when you want standard ADO.NET patterns, parameterized commands, and connection-per-request integration with existing .NET code.

If you keep that split in mind, CSharpDB is straightforward to use from multiple threads: one writer at a time, many readers at once, and clear ownership for explicit transactions.