SQLite persistence: a failed WriteToStorageKey leaves its transaction open on a pooled connection, so later writes fail or are silently lost

Author: NSTA1Created Sep 24, 2026Updated Sep 24, 2026

Summary

In the SQLite persistence script, WriteToStorageKey opens its transaction with BEGIN TRANSACTION in the query text and ends it with COMMIT. If any statement in between fails, COMMIT never runs. The System.Data.SQLite invariant resolves to Microsoft.Data.Sqlite, which pools connections by default and does not roll back a transaction that was opened in command text. So the connection goes back into the pool with the transaction still open, and every later operation that draws that connection runs inside it.

The usual trigger is ordinary writer contention. The UPDATE waits for the write lock past the command timeout and fails with SQLite Error 5: 'database is locked'. That write fails cleanly. The damage is done to the operations that come after it on the same connection:

Writes fail. Every WriteToStorageKey on that connection fails with SQLite Error 1: 'cannot start a transaction within a transaction', even when there is no contention at all.
Clears are acknowledged but never committed. ClearStorageKey has no BEGIN of its own, so on that connection it runs inside the leaked transaction. It returns the new version, so AdoNetGrainStorage records success, but nothing is committed. Reads on the same connection see the cleared state; no other connection does. From then on the connection also holds the database write lock.
It spreads. Once a clear has run on it, writes on other pooled connections time out with database is locked, and each of those failures leaves one more connection with an open transaction.
Cleared state comes back. When the pool finally closes the connection, SQLite rolls the transaction back and undoes the acknowledged clear. The grain's next write fails with InconsistentStateException, and after reactivation it reads back the state it was told had been cleared.

Affected code

WriteToStorageKey in src/AdoNet/Orleans.Persistence.AdoNet/Sqlite-Persistence.sql. The BEGIN TRANSACTION / COMMIT wrapper is present on main, v10.3.1 and v10.2.2, and has been since #9846. This is the shape on main:

sql

BEGIN TRANSACTION;
CREATE TEMP TABLE IF NOT EXISTS OrleansStorageWriteState (...);
DELETE FROM OrleansStorageWriteState;
INSERT INTO OrleansStorageWriteState (TotalChangesBefore) VALUES (total_changes() + 1);
UPDATE OrleansStorage SET ... WHERE ... AND Version = @GrainStateVersion;   -- SQLITE_BUSY here under contention
INSERT INTO OrleansStorage (...) SELECT ... WHERE changes() = 0 AND @GrainStateVersion IS NULL AND NOT EXISTS (...);
SELECT ... AS NewGrainStateVersion WHERE total_changes() > ...;
SELECT @GrainStateVersion AS NewGrainStateVersion WHERE total_changes() = ... AND @GrainStateVersion IS NOT NULL;
COMMIT;

Why the transaction stays open

Four things combine. The links are to dotnet/efcore main. The behaviour is the same in Microsoft.Data.Sqlite 10.0.11, which the repro below uses.

When a statement inside an explicit transaction fails with SQLITE_BUSY, SQLite does not end the transaction. The result-code documentation says the caller should roll it back.
SqliteDataReader.NextResult() stops the batch at the first error. Its catch resets the statement, drops the statement enumerator, disposes the reader and rethrows. Nothing after the failing statement runs, including COMMIT.
SqliteConnection.Close() rolls back only a transaction opened through the ADO.NET API (Transaction?.Dispose()). A transaction opened by BEGIN in command text is not tracked, although the method's doc comment says "Open transactions are rolled back".
Returning the connection to the pool goes through SqliteConnectionPool.Return, SqliteConnectionInternal.Deactivate and SqliteConnection.Deactivate. These only unregister collations, functions, aggregates and extensions. Nothing checks sqlite3_get_autocommit or rolls back, so the connection goes back into the warm pool with its transaction still open.

RelationalStorage.ExecuteAsync opens a new DbConnection for each operation and disposes it afterwards, so every storage call returns its connection to that pool. The SQLite invariant is mapped to Microsoft.Data.Sqlite.SqliteFactory.

The other dialects are not exposed in the same way. The SQL Server script sets XACT_ABORT ON inside its transaction. The MySQL WriteToStorage procedure declares EXIT HANDLER ... BEGIN ROLLBACK; RESIGNAL; END. PostgreSQL does the whole write in a single function call. SQLite has no way to handle an error inside a batch, so this script cannot clean up after itself.

Repro

The program below hosts a single Orleans silo that uses AddAdoNetGrainStorage over SQLite (the System.Data.SQLite invariant, with Microsoft.Data.Sqlite pooling left at its default). It loads Sqlite-Main.sql and Sqlite-Persistence.sql into a file database, then drives a grain through WriteStateAsync, ClearStateAsync and ReadStateAsync. A second, unpooled connection takes the write lock to create contention, and a third reads the stored row directly. The only change to the storage defaults is a command timeout of 2 s instead of 30 s, so it finishes quickly.

The output below uses Microsoft.Orleans.Server and Microsoft.Orleans.Persistence.AdoNet 10.3.1 with the v10.3.1 script. The other versions were Microsoft.Data.Sqlite 10.0.11, SQLitePCLRaw.bundle_e_sqlite3 3.0.5 and SQLite 3.53.4. The output is the same with the script from main, and with journal_mode set to DELETE (the default) or WAL. Only the activation ids differ between runs.

Microsoft.Orleans.Persistence.AdoNet 10.3.1, sqlite 3.53.4, journal_mode delete, script scripts-v10.3.1
[1] grain 42 WriteStateAsync (Value=1): activation 367d7561, Value=1, ETag=1, RecordExists=True
[2] another connection takes the write lock (BEGIN IMMEDIATE)
[3] grain 42 WriteStateAsync (Value=2) while the lock is held: threw OrleansException (inner SqliteException: SQLite Error 5: 'database is locked'.)
[4] lock released; the database is uncontended from here on
[5] sqlite3_get_autocommit on the pooled connection = 0
[6] grain 42 WriteStateAsync (Value=3): threw OrleansException (inner SqliteException: SQLite Error 1: 'cannot start a transaction within a transaction'.)
[7] grain 42 ClearStateAsync: activation 367d7561, Value=0, ETag=2, RecordExists=False
    stored row (independent connection): grain 42 Version=1 payload={"$id":"1","$type":"CounterState, OrleansSqliteE2E","Value":1}
[8] grain 42 ReadStateAsync: activation 367d7561, Value=0, ETag=2, RecordExists=False
[9] grain 43 WriteStateAsync (Value=1, first write): threw OrleansException (inner SqliteException: SQLite Error 5: 'database is locked'.)
[10] pooled connections with an open transaction: 2 of 3
[11] SqliteConnection.ClearAllPools(); stored row (independent connection): grain 42 Version=1 payload={"$id":"1","$type":"CounterState, OrleansSqliteE2E","Value":1}
[12] grain 42 WriteStateAsync (Value=4): threw InconsistentStateException: Version conflict (WriteState): ServiceId=default ProviderName=sqlite GrainType=counter GrainId=42 ETag=2.
[13] grain 42 state: activation 445daf7c, Value=1, ETag=1, RecordExists=True
[14] grain 42 WriteStateAsync (Value=5): activation 445daf7c, Value=5, ETag=2, RecordExists=True

[3] is the expected, clean failure. Everything after it is fallout.
[5] The BEGIN from [3] is still open on the pooled connection.
[6] Writes on that connection now fail with no contention at all.
[7] ClearStateAsync succeeds and the grain's ETag moves to 2, but the stored row still has version 1 and the old payload.
[8] A ReadStateAsync that gets the same connection sees the cleared state, which no other connection can see.
[9] and [10] The uncommitted clear holds the write lock, so the first write of an unrelated grain times out, and its connection is left with an open transaction too.
[11] Once the pool closes its connections, SQLite rolls the clear back.
[12] and [13] The grain's ETag (2) is now ahead of the stored row (1), so its next write fails with InconsistentStateException. The grain is deactivated, and the new activation reads back the state it was told had been cleared.
[14] With the poisoned connections gone from the pool, writes work again. Without an explicit ClearAllPools(), a connection only leaves the pool once it sits unused through two of the pool's prune cycles (2 to 4 minutes each), so a connection that keeps being reused stays poisoned.

OrleansSqliteE2E.csproj

xml

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <ManagePackageVersionsCentrally>false</ManagePackageVersionsCentrally>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Orleans.Server" Version="10.3.1" />
    <PackageReference Include="Microsoft.Orleans.Persistence.AdoNet" Version="10.3.1" />
    <PackageReference Include="Microsoft.Data.Sqlite" Version="10.0.11" />
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="3.0.5" />
  </ItemGroup>

</Project>

Program.cs

csharp

// End-to-end repro: a real Orleans silo with AdoNetGrainStorage over SQLite (Microsoft.Data.Sqlite, default pooling).
// Usage: dotnet run -- <directory containing Sqlite-Main.sql and Sqlite-Persistence.sql> [delete|wal]
using System.Reflection;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using SQLitePCL;

var scriptsDir = args[0];
var journalMode = args.Length > 1 ? args[1] : "delete";
var dbPath = Path.Combine(Path.GetTempPath(), $"orleans-sqlite-e2e-{Guid.NewGuid():N}.db");

// The storage provider's connection string: pooling at its default, 2 s command timeout instead of 30 s.
var storageCs = new SqliteConnectionStringBuilder { DataSource = dbPath, Mode = SqliteOpenMode.ReadWriteCreate, DefaultTimeout = 2 }.ToString();
var independentCs = new SqliteConnectionStringBuilder { DataSource = dbPath, Mode = SqliteOpenMode.ReadWriteCreate, Pooling = false, DefaultTimeout = 1 }.ToString();

using (var c = new SqliteConnection(independentCs))
{
    c.Open();
    Exec(c, $"PRAGMA journal_mode={journalMode};");
    Exec(c, File.ReadAllText(Path.Combine(scriptsDir, "Sqlite-Main.sql")));
    Exec(c, File.ReadAllText(Path.Combine(scriptsDir, "Sqlite-Persistence.sql")));
    var orleansVersion = typeof(Orleans.Storage.AdoNetGrainStorage).Assembly.GetCustomAttribute<AssemblyInformationalVersionAttribute>()?.InformationalVersion;
    Console.WriteLine($"Microsoft.Orleans.Persistence.AdoNet {orleansVersion?.Split('+', ' ')[0].TrimEnd('.')}, sqlite {Scalar(c, "SELECT sqlite_version();")}, journal_mode {Scalar(c, "PRAGMA journal_mode;")}, script {Path.GetFileName(scriptsDir)}");
}

var builder = Host.CreateApplicationBuilder();
builder.Logging.ClearProviders();
builder.UseOrleans(silo => silo
    .UseLocalhostClustering(siloPort: 22111, gatewayPort: 32000)
    .AddAdoNetGrainStorage("sqlite", o =>
    {
        o.Invariant = "System.Data.SQLite";
        o.ConnectionString = storageCs;
    }));

using var host = builder.Build();
await host.StartAsync();
var grains = host.Services.GetRequiredService<IGrainFactory>();
var g42 = grains.GetGrain<ICounterGrain>(42);
var g43 = grains.GetGrain<ICounterGrain>(43);

Step(1, "grain 42 WriteStateAsync (Value=1)", await Call(() => g42.Set(1)));
using (var blocker = new SqliteConnection(independentCs))
{
    blocker.Open();
    Exec(blocker, "BEGIN IMMEDIATE;");
    Console.WriteLine("[2] another connection takes the write lock (BEGIN IMMEDIATE)");
    Step(3, "grain 42 WriteStateAsync (Value=2) while the lock is held", await Call(() => g42.Set(2)));
    Exec(blocker, "ROLLBACK;");
    Console.WriteLine("[4] lock released; the database is uncontended from here on");
}

Console.WriteLine($"[5] sqlite3_get_autocommit on the pooled connection = {PooledAutocommit()}");
Step(6, "grain 42 WriteStateAsync (Value=3)", await Call(() => g42.Set(3)));
Step(7, "grain 42 ClearStateAsync", await Call(() => g42.Clear()));
Console.WriteLine($"    stored row (independent connection): {Stored()}");
Step(8, "grain 42 ReadStateAsync", await Call(() => g42.Reload()));
using (var held = new SqliteConnection(storageCs))
{
    held.Open(); // hold the poisoned connection so the next storage call is handed a fresh pooled connection
    Step(9, "grain 43 WriteStateAsync (Value=1, first write)", await Call(() => g43.Set(1)));
}

Console.WriteLine($"[10] pooled connections with an open transaction: {CountPoisoned(3)} of 3");
SqliteConnection.ClearAllPools();
Console.WriteLine($"[11] SqliteConnection.ClearAllPools(); stored row (independent connection): {Stored()}");
Step(12, "grain 42 WriteStateAsync (Value=4)", await Call(() => g42.Set(4)));
await Task.Delay(500);
Step(13, "grain 42 state", await Call(() => g42.Describe()));
Step(14, "grain 42 WriteStateAsync (Value=5)", await Call(() => g42.Set(5)));

await host.StopAsync();
SqliteConnection.ClearAllPools();
File.Delete(dbPath);
return;

int PooledAutocommit()
{
    using var c = new SqliteConnection(storageCs);
    c.Open();
    return raw.sqlite3_get_autocommit(c.Handle);
}

int CountPoisoned(int n)
{
    var conns = Enumerable.Range(0, n).Select(_ => { var c = new SqliteConnection(storageCs); c.Open(); return c; }).ToList();
    var poisoned = conns.Count(c => raw.sqlite3_get_autocommit(c.Handle) == 0);
    conns.ForEach(c => c.Dispose());
    return poisoned;
}

object? Stored()
{
    using var c = new SqliteConnection(independentCs);
    c.Open();
    return Scalar(c, "SELECT group_concat('grain ' || GrainIdN1 || ' Version=' || Version || ' payload=' || CASE WHEN PayloadBinary IS NULL THEN 'NULL' ELSE CAST(PayloadBinary AS TEXT) END, '; ') FROM OrleansStorage;");
}

static async Task<string> Call(Func<Task<string>> call)
{
    try
    {
        return await call();
    }
    catch (Exception ex)
    {
        var inner = ex;
        while (inner.InnerException is not null)
        {
            inner = inner.InnerException;
        }

        var outer = ex.GetType().Name;
        var detail = inner.Message.Split('\n')[0].Trim();
        return ReferenceEquals(inner, ex) ? $"threw {outer}: {detail}" : $"threw {outer} (inner {inner.GetType().Name}: {detail})";
    }
}

static void Step(int n, string what, string outcome) => Console.WriteLine($"[{n}] {what}: {outcome}");

static void Exec(SqliteConnection c, string sql)
{
    using var cmd = c.CreateCommand();
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
}

static object? Scalar(SqliteConnection c, string sql)
{
    using var cmd = c.CreateCommand();
    cmd.CommandText = sql;
    return cmd.ExecuteScalar();
}

public interface ICounterGrain : IGrainWithIntegerKey
{
    Task<string> Set(int value);

    Task<string> Clear();

    Task<string> Reload();

    Task<string> Describe();
}

[GenerateSerializer]
public sealed class CounterState
{
    [Id(0)]
    public int Value { get; set; }
}

public sealed class CounterGrain([PersistentState("counter", "sqlite")] IPersistentState<CounterState> state) : Grain, ICounterGrain
{
    private readonly string _activation = Guid.NewGuid().ToString("N")[..8];

    public async Task<string> Set(int value)
    {
        state.State.Value = value;
        await state.WriteStateAsync();
        return Current();
    }

    public async Task<string> Clear()
    {
        await state.ClearStateAsync();
        return Current();
    }

    public async Task<string> Reload()
    {
        await state.ReadStateAsync();
        return Current();
    }

    public Task<string> Describe() => Task.FromResult(Current());

    private string Current() => $"activation {_activation}, Value={state.State.Value}, ETag={state.Etag ?? "null"}, RecordExists={state.RecordExists}";
}

The obvious workaround makes things worse

I vendor this script. My first workaround was to remove BEGIN TRANSACTION and COMMIT so that each statement auto-commits. That caused a different bug, which I describe here so nobody else tries it. The INSERT after the UPDATE is a write statement even when its WHERE matches nothing, so it has to take the write lock again after the UPDATE has already committed. Under contention that second wait can run past the timeout. The batch then throws database is locked for a write that is already durable. The grain keeps its old ETag, and its next write fails with InconsistentStateException: Version conflict (WriteState). I saw exactly this in my deployment.

SAVEPOINT / RELEASE opens the same kind of untracked transaction, so it leaks the same way. BEGIN IMMEDIATE moves the lock wait onto the BEGIN itself, which covers the common case. It does not help when something fails after BEGIN: a COMMIT that times out waiting for readers in rollback-journal mode, SQLITE_FULL, or an I/O error still leaves the transaction open.

Suggested fix

Make the write a single statement, so there is no explicit transaction to leak. SQLite runs a trigger's actions as part of the statement that fires it, so the script can:

stage the parameters as one row in a connection-private TEMP table;
run the UPDATE or the conditional INSERT from a TEMP trigger on that table, recording changes() in the staged row;
report NewGrainStateVersion from the staged row.

The staging INSERT, the storage change and the bookkeeping then commit or roll back together, in one implicit transaction that takes the write lock once. If that statement fails, SQLite ends the transaction itself, so nothing is left open. Every statement after it reads only the connection-private temp table, which needs no database lock.

Some details that matter:

A TEMP trigger is allowed to modify a table in the main database.
Bound parameters are not visible inside a trigger body, so they are passed in as columns of the staged row.
The trigger sets the staged PayloadBinary to NULL in the same statement, so an idle pooled connection does not keep the last grain state it wrote.
An INSERT ... ON CONFLICT upsert is not an option, because IX_OrleansStorage is not unique.

Here is the complete replacement for WriteToStorageKey. No other query changes.

sql

-- Updates an existing grain state with optimistic concurrency control or inserts it if it does not exist.
INSERT INTO OrleansQuery (QueryKey, QueryText) VALUES
('WriteToStorageKey', '
    CREATE TEMP TABLE IF NOT EXISTS Orlea

Source: dotnet/orleans

View original on GitHub View discussion on GitHub