---
title: "Transactions in C# with SQLite: commit and rollback"
url: https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/
description: "SQLite transactions in C# with OneLite: BeginTransaction, Commit and Rollback through ADO.NET, and WriteAsync, which commits or rolls back for you. A money transfer that commits and one that rolls back, with real output and a sqlite3 check."
version: "0.2.0-preview.2"
---

# Transactions in C# with SQLite: commit and rollback

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](#writeasync-commits-or-rolls-back-for-you)).

```shell
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`:

```csharp
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:

```csharp
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:

```shell
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](https://recursiveai.org/onelite/docs/data/) · [The happy path](https://recursiveai.org/onelite/docs/happy-path/) · [All recipes](https://recursiveai.org/onelite/cookbook/)