Transactions in C# with SQLite: commit and rollback · onelite cookbook

Start a transaction with BeginTransaction(), run your commands, then Commit() to keep them or Rollback() to undo them all. This uses OneLite’s ADO.NET provider; the happy-path WriteAsync does the same with less code (below).

Terminal

dotnet new console -o SqliteTransactions && cd SqliteTransactions
dotnet add package RecursiveAI.OneLite.Data --version 0.2.0-preview.2

Put this in Program.cs and run dotnet run:

using System.Data.Common;
using RecursiveAI.OneLite.Data;
 
using var conn = new OneLiteConnection("Data Source=bank.db");
conn.Open();
Run("create table accounts (name text primary key, balance integer not null)");
Run("insert into accounts values ('alice', 100), ('bob', 0)");
 
Transfer(30);    // alice has enough: committed
Transfer(500);   // she doesn't: rolled back, nothing changes
Console.WriteLine($"alice {Balance("alice")}, bob {Balance("bob")}");
 
void Transfer(long amount)
{
    using DbTransaction tx = conn.BeginTransaction();   // commands join it
    Run("update accounts set balance = balance - @a where name = 'alice'", amount);
    Run("update accounts set balance = balance + @a where name = 'bob'", amount);
    if (Balance("alice") < 0)
    {
        tx.Rollback();
        Console.WriteLine($"transfer {amount}: rolled back");
        return;
    }
    tx.Commit();
    Console.WriteLine($"transfer {amount}: committed");
}
 
void Run(string sql, long amount = 0)
{
    using DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    cmd.Parameters.Add(new OneLiteParameter("@a", amount));
    cmd.ExecuteNonQuery();
}
 
long Balance(string name)
{
    using DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select balance from accounts where name = @n";
    cmd.Parameters.Add(new OneLiteParameter("@n", name));
    return (long)cmd.ExecuteScalar()!;
}

Text

transfer 30: committed
transfer 500: rolled back
alice 70, bob 30

Inside the transaction, the second transfer’s own reads see its uncommitted changes (alice at -430), which is how it knows to roll back. After Rollback() both updates are gone.

BeginTransaction() runs BEGIN DEFERRED: the write lock is taken at the first write. BeginTransaction(deferred: false) runs BEGIN IMMEDIATE and takes it at once.
Disposing a transaction you didn’t commit rolls it back, so an exception inside the using block undoes everything.
One transaction per connection: a second BeginTransaction() before the first ends throws. Use SAVEPOINT and RELEASE in SQL for nesting.
A command created after BeginTransaction() joins the transaction by itself; one created before it needs cmd.Transaction = tx.

WriteAsync commits or rolls back for you

With the happy path (RecursiveAI.OneLite, already brought in by the provider), WriteAsync runs a delegate in one write transaction: it commits when the delegate returns and rolls back when it throws. Replace Program.cs with this and run dotnet run again:

using RecursiveAI.OneLite;
 
await using var db = await Database.OpenAsync("bank.db");
 
await db.WriteAsync(async tx =>
{
    long amount = 20;   // bob pays alice back
    await tx.ExecuteAsync(
        $"update accounts set balance = balance - {amount} where name = 'bob'");
    await tx.ExecuteAsync(
        $"update accounts set balance = balance + {amount} where name = 'alice'");
});
 
try
{
    await db.WriteAsync(async tx =>
    {
        await tx.ExecuteAsync($"update accounts set balance = 0 where name = 'alice'");
        throw new InvalidOperationException("changed my mind");
    });
}
catch (InvalidOperationException e) { Console.WriteLine($"rolled back: {e.Message}"); }
 
foreach (string row in await db.QueryAsync<string>(
             $"select name || ' ' || balance from accounts order by name"))
    Console.WriteLine(row);
Console.WriteLine(await db.ScalarAsync<string>($"pragma journal_mode"));

Text

rolled back: changed my mind
alice 90
bob 10
wal

Database.OpenAsync opens bank.db in WAL mode, the happy-path default, and the mode stays with the file: the last line says so. If the database is busy, WriteAsync rolls back and runs the delegate again after a short backoff, up to 8 times by default (MaxWriteAttempts). So keep the delegate to database work: no emails sent or files written from inside it. Calling db itself inside the delegate throws; use the tx it receives.

Check it with sqlite3

The sqlite3 shell (3.53.4, from sqlite.org) sees the committed balances, in WAL mode, and nothing from the rolled-back transactions:

Terminal

sqlite3 bank.db "select * from accounts" "pragma journal_mode" "pragma integrity_check"

Text

alice|90
bob|10
wal
ok

Limits

A preview: APIs may change between previews.
BEGIN CONCURRENT (several writers at once) is not in this release. In the README’s words it is “still to come before 1.0”.

ADO.NET provider docs · The happy path · All recipes