Transactions with Dapper: BeginTransaction, Commit, Rollback and TransactionScope

Version note: The code in this post targets Dapper 2.1 and .NET 10.

Guess First

Ada orders 5 copies of "The Left Hand of Darkness", which has only 4 left in stock. The code runs in three steps: insert the order row, insert the order item, decrease the stock. The books.stock column has a check (stock >= 0) constraint, so the third step is going to blow up. The question: when the program finishes, how many rows are in the orders and order_items tables?

await using var conn = new SqliteConnection(cs);
await conn.OpenAsync();

try
{
    var orderId = await conn.ExecuteScalarAsync<long>(
        "insert into orders (customer, created_at) values (@customer, @createdAt) returning id",
        new { customer = "Ada", createdAt = "2026-11-04T10:00:00Z" });

    await conn.ExecuteAsync(
        "insert into order_items (order_id, book_id, quantity, unit_price) values (@orderId, @bookId, @quantity, @unitPrice)",
        new { orderId, bookId = 2, quantity = 5, unitPrice = 12.50 });

    await conn.ExecuteAsync(
        "update books set stock = stock - @quantity where id = @bookId",
        new { bookId = 2, quantity = 5 });
}
catch (SqliteException ex)
{
    Console.WriteLine(ex.Message);
}

var orders = await conn.ExecuteScalarAsync<int>("select count(*) from orders");
var items = await conn.ExecuteScalarAsync<int>("select count(*) from order_items");
Console.WriteLine($"orders: {orders}, order_items: {items}");

Before the answer, let's look at the setup for this post.

Setup

The packages are the same as in the previous post: Dapper 2.1.89 and Microsoft.Data.Sqlite 10.0.14. This time we need books with stock, plus orders and order_items. Every example starts with const string cs = "Data Source=bookstore.db"; and await Setup.CreateAsync(cs);:

using Dapper;
using Microsoft.Data.Sqlite;

public static class Setup
{
    public static async Task CreateAsync(string cs)
    {
        if (File.Exists("bookstore.db")) File.Delete("bookstore.db");
        await using var conn = new SqliteConnection(cs);
        await conn.ExecuteAsync("""
            create table books (
                id    integer primary key,
                title text not null,
                price real not null,
                stock integer not null check (stock >= 0)
            );
            create table orders (
                id         integer primary key,
                customer   text not null,
                created_at text not null
            );
            create table order_items (
                id         integer primary key,
                order_id   integer not null references orders(id),
                book_id    integer not null references books(id),
                quantity   integer not null,
                unit_price real not null
            );
            insert into books (id, title, price, stock) values
                (1, 'A Wizard of Earthsea', 9.99, 12),
                (2, 'The Left Hand of Darkness', 12.50, 4),
                (3, 'Dune', 14.99, 20);
            """);
    }
}

The Answer: A Half-Written Order

SQLite Error 19: 'CHECK constraint failed: stock >= 0'.
orders: 1, order_items: 1

The stock couldn't be decreased, but the order and its item are sitting in the database. A "your order has been received" email is about to go out for a book that isn't in the warehouse. The reason is simple: when you don't open a transaction, every statement is a transaction of its own and is committed as soon as it finishes. From the database's point of view you did three independent pieces of work; two succeeded and one didn't.

The promise of a transaction is exactly to prevent this: the statements inside it either all become permanent together or none of them do. The bank-transfer analogy is a cliché, but it's accurate: money can't leave one account and be left hanging before it reaches the other.

BeginTransaction and the transaction: Parameter

Dapper has no transaction API of its own. ADO.NET itself opens the transaction; you just tell Dapper, on every call, which transaction to run in. Let's move the same order flow into a method and put it in a transaction:

static async Task<long> PlaceOrderAsync(DbConnection conn, string customer, int bookId, int quantity)
{
    await using var tx = await conn.BeginTransactionAsync();

    var orderId = await conn.ExecuteScalarAsync<long>(
        "insert into orders (customer, created_at) values (@customer, @createdAt) returning id",
        new { customer, createdAt = DateTime.UtcNow.ToString("O") },
        transaction: tx);

    await conn.ExecuteAsync(
        """
        insert into order_items (order_id, book_id, quantity, unit_price)
        select @orderId, id, @quantity, price from books where id = @bookId
        """,
        new { orderId, bookId, quantity },
        transaction: tx);

    await conn.ExecuteAsync(
        "update books set stock = stock - @quantity where id = @bookId",
        new { bookId, quantity },
        transaction: tx);

    await tx.CommitAsync();
    return orderId;
}

We call it with two customers; Ada again wants 5 copies, Alan wants 3:

await using var conn = new SqliteConnection(cs);
await conn.OpenAsync();

await TryPlaceOrderAsync(conn, "Ada", bookId: 2, quantity: 5);
await TryPlaceOrderAsync(conn, "Alan", bookId: 2, quantity: 3);
await PrintAsync(conn);

static async Task TryPlaceOrderAsync(DbConnection conn, string customer, int bookId, int quantity)
{
    try
    {
        var orderId = await PlaceOrderAsync(conn, customer, bookId, quantity);
        Console.WriteLine($"{customer}: order {orderId} committed");
    }
    catch (SqliteException ex)
    {
        Console.WriteLine($"{customer}: rolled back ({ex.SqliteErrorCode})");
    }
}

static async Task PrintAsync(DbConnection conn)
{
    var orders = await conn.ExecuteScalarAsync<int>("select count(*) from orders");
    var items = await conn.ExecuteScalarAsync<int>("select count(*) from order_items");
    var stock = await conn.ExecuteScalarAsync<int>("select stock from books where id = 2");
    Console.WriteLine($"orders: {orders}, order_items: {items}, stock: {stock}");
}

Ada: rolled back (19)
Alan: order 1 committed
orders: 1, order_items: 1, stock: 1

Not a trace of Ada's order is left, and Alan's was written in full. Notice that we never call RollbackAsync anywhere in the code. When the update threw, execution never reached CommitAsync; the transaction was disposed on the way out of the await using block, and disposing an uncommitted transaction means a rollback. Writing RollbackAsync in an explicit catch is valid too, but the "anything that doesn't reach the commit is rolled back" pattern is shorter and doesn't get forgotten when a new failure path is added.

A small detail: Alan's order got number 1. Ada's insert had also taken number 1, but that row vanished with the rollback. On SQLite an integer primary key without AUTOINCREMENT hands out the largest id plus one, so the number was reused. SQL Server's IDENTITY, on the other hand, doesn't rewind on rollback and leaves a gap. Don't rely on ids being consecutive on any database.

Also notice that we opened the connection explicitly. As we saw in the previous post, Dapper opens and closes a closed connection for every call, while a transaction lives on an open connection. BeginTransactionAsync requires an open connection anyway.

If You Forget the transaction: Parameter

The most common mistake in real code is forgetting transaction: tx on one of the three calls. Let's see what happens on SQLite:

await using (var tx = await conn.BeginTransactionAsync())
{
    try
    {
        // transaction: tx is missing
        await conn.ExecuteAsync("update books set stock = 0 where id = 1");
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.Message);
    }
}

await using (var tx = await conn.BeginTransactionAsync())
{
    await conn.ExecuteAsync("update books set stock = 0 where id = 1", transaction: tx);
}
Console.WriteLine($"stock after dispose: {await conn.ExecuteScalarAsync<int>("select stock from books where id = 1")}");

stock after dispose: 12

No exception in the first block; the catch never ran. Microsoft.Data.Sqlite's CreateCommand method assigns the connection's active transaction to the new command's Transaction property by itself, and Dapper only touches that property when the transaction argument is not null. The result: on SQLite a forgotten transaction: silently joins the right transaction. The second block shows that a transaction disposed without a commit really is rolled back; the stock stayed at 12.

This isn't a convenience, it's a trap. In the same situation SqlClient leaves the command as it is and throws an InvalidOperationException saying the connection has a pending local transaction but the command doesn't. So code you wrote on SQLite, which passes its tests, breaks the day it moves to SQL Server. Write transaction: tx on every call; don't leave it to a provider's good will.

A Short Look at Isolation Levels

The isolation level determines how much concurrent transactions can see of each other's unfinished work. You can pass an IsolationLevel to BeginTransactionAsync. On SQLite that value is a request, not an order:

await using (var tx = await conn.BeginTransactionAsync(IsolationLevel.ReadCommitted))
{
    Console.WriteLine($"requested ReadCommitted, got {tx.IsolationLevel}");
    await conn.ExecuteAsync("update books set stock = 0 where id = 1", transaction: tx);

    await using var other = new SqliteConnection(cs);
    var seen = await other.ExecuteScalarAsync<int>("select stock from books where id = 1");
    Console.WriteLine($"other connection sees: {seen}");
}

try
{
    await using var tx = await conn.BeginTransactionAsync(IsolationLevel.Snapshot);
}
catch (Exception ex)
{
    Console.WriteLine($"{ex.GetType().Name}: {ex.Message}");
}

requested ReadCommitted, got Serializable
other connection sees: 12
ArgumentException: The IsolationLevel 'Snapshot' is not supported.

Microsoft.Data.Sqlite treats the level you ask for as a minimum and promotes it to either ReadUncommitted (only with a shared cache) or Serializable; it doesn't support Snapshot and Chaos at all. The second connection sees the old 12, not the uncommitted 0; there is no dirty read. On SQL Server and PostgreSQL the default level is ReadCommitted, and the options genuinely behave differently; their locking and versioning details are provider-specific topics, and we will come back to them in the relevant posts.

Know SQLite's real limitation too: only one transaction can write at a time. A long-running write transaction makes the other writers wait, and when the wait times out you get a "database is locked" error. Keep transactions short: compute first, then open, write and commit.

TransactionScope and a Note on Async

As an alternative to the ADO.NET transaction there is System.Transactions.TransactionScope. When the scope opens, an ambient transaction is created, connections opened inside that scope join it automatically, and you don't deal with the transaction: parameter. But there is a setting you need to know about in async code:

using System.Transactions;

try
{
    using var scope = new TransactionScope();
    await Task.Delay(10);
    scope.Complete();
}
catch (InvalidOperationException ex)
{
    Console.WriteLine(ex.Message);
}

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    await Task.Delay(10);
    Console.WriteLine($"ambient: {Transaction.Current?.TransactionInformation.Status}");
    scope.Complete();
}

A TransactionScope must be disposed on the same thread that it was created.
ambient: Active

The default TransactionScope keeps the ambient transaction bound to the thread. When the code continues on a different thread after an await, the transaction is lost and the dispose throws this exception. TransactionScopeAsyncFlowOption.Enabled carries the ambient transaction along with the async flow. If you use TransactionScope in async code, this parameter is mandatory.

Now let's try the same thing with SQLite. We deliberately don't call Complete(), so we expect everything to be rolled back:

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    await using var conn = new SqliteConnection(cs);
    await conn.ExecuteAsync("update books set stock = 0 where id = 1");
    // scope.Complete() is never called
}

await using var check = new SqliteConnection(cs);
var stock = await check.ExecuteScalarAsync<int>("select stock from books where id = 1");
Console.WriteLine($"stock: {stock}");

stock: 0

The stock was zeroed and stayed that way. Microsoft.Data.Sqlite doesn't support System.Transactions yet; its documentation says so explicitly and recommends ADO.NET transactions. The connection doesn't join the scope, the update commits on its own as usual, and you get no error either. SqlClient does support enlisting in a TransactionScope, but if you don't want to entrust a business rule to behavior that varies by provider, BeginTransaction is the most predictable path with Dapper.

Common Pitfalls

Forgetting transaction: tx on one call. It passes silently on SQLite and throws on SqlClient. Write it on every call; if you pass the transaction into repository methods, a DbTransaction? tx = null signature makes it harder to forget.

Catching an error inside a transaction and carrying on. After a failing statement the state of the transaction depends on the provider; on PostgreSQL the transaction enters an error state and rejects subsequent commands until it is rolled back. If you got an error, drop the transaction and start over.

Making a network call, sending an email or waiting for user confirmation while the transaction is open. Locks are held as long as the transaction stays open; on SQLite the other writers wait entirely. Work that touches the outside world happens before or after the commit, not inside it.

Using TransactionScope in async code without TransactionScopeAsyncFlowOption.Enabled, and not knowing that the scope has no effect on SQLite.

Trying to reuse a transaction after a commit. A committed or rolled-back transaction is done; open a new transaction for new work.

For those coming from EF Core: SaveChanges already writes changes inside a single transaction, so in most scenarios you don't even think about transactions. In Dapper every Execute is a separate piece of work; if several statements must form a whole, you set up the transaction. This is the most concrete example of Dapper's "you draw the SQL and its boundaries" philosophy.

A Small Experiment

★☆☆ Remove transaction: tx from the third call in PlaceOrderAsync and try Ada's order again. See that the result doesn't change, then write a comment in your own words on why this is a lucky break specific to SQLite.

★★☆ Let an order take several items: change PlaceOrderAsync to take a (int BookId, int Quantity)[]. Make the first item succeed and the second fail for lack of stock, and verify that no item was written.

★★★ Open two separate connections and add Default Timeout=1 to the connection string. Open a transaction on one, run an update and don't commit; try an update from the other. Read the error message you get and explain why keeping transactions short matters so much on SQLite.

So far we have always mapped one table to one class. But an order item points to a book, a book points to an author, and we want to read them in a single query with a JOIN. In the next post we will produce several objects from a single row with multi-mapping and splitOn, and take a close look at the N+1 problem. The series will keep being updated as new Dapper versions ship.