SQLite ADO.NET Connection Lifetimes: Ownership and Disposal

Unclear ADO.NET connection lifetimes can make a SQLite database is locked error look like proof that the database needs a larger timeout, more retries, or one global application lock. Those changes may hide a symptom without answering the more important ADO.NET questions: who owns the connection, who closes the reader, where does the transaction end, and does the exception path release every nested resource? A reader that remains active, a transaction that survives longer than its unit of work, or a provider object shared by several threads can turn ordinary short contention into an opaque failure.

This article therefore concentrates on the resource-lifetime contract rather than repeating a general WAL tuning guide or an EF Core migration procedure. WAL and a bounded busy timeout remain relevant operating conditions, but neither substitutes for ownership. The reproducible example uses .NET SDK 10.0.400, Microsoft.Data.Sqlite 10.0.0, synthetic rows, and a fresh temporary directory. It never connects to an existing database or external service, and its single run is not presented as a production throughput result.

Core answer: give each unit of work its own connection

The dependable rule for ADO.NET connection lifetimes is that each concurrent unit of work creates and disposes its own connection, transaction, command, and reader; provider objects are never used concurrently by different threads. WAL can improve reader-writer overlap and a timeout can absorb brief contention, bounded ownership ends the caller’s right to use those objects. Physical handle closure additionally depends on pooling and other connections.

“One unit of work, one connection” does not mean that every SQL statement needs a permanently new physical session, nor does it prohibit a provider from pooling connections. It defines the application-level right to use an object. A method obtains a connection, performs a coherent read or write operation, materializes the result, and leaves the scope. After disposal, the caller no longer owns or uses that object even if the provider internally returns an underlying resource to a pool.

A singleton helper that stores one open connection is attractive because it appears to avoid setup cost. Under concurrency, however, commands, active readers, transaction state, parameters, failures, and close operations can interleave on that shared object. Microsoft’s official Microsoft.Data.Sqlite error guidance states that SqliteConnection, SqliteCommand, and SqliteDataReader objects are not thread-safe and must not be shared for concurrent use. Scattered locks around selected helper methods are weaker than removing shared ownership because an unguarded path can still escape.

A connection factory is a clearer abstraction. The factory stores no open provider object; it creates one for the current operation. A repository can then open that connection inside a using or await using scope, while a larger unit of work may explicitly pass one connection and transaction to cooperating repositories. Both designs make ownership visible. What should be avoided is ambient mutable state in which any method can silently borrow a global current connection.

This boundary also improves diagnostics. A worker identifier, start time, transaction outcome, retry count, and elapsed time can all describe one operation. With a shared open connection, an event may report state left by a different worker, making logs misleading. The first acceptance criterion should therefore be structural: no worker can obtain another worker’s ADO.NET object reference. “No locked message appeared” is too weak because a leak may simply not have encountered the triggering timing yet.

Dispose readers before commands, transactions, and connections

Resources should be nested in creation order and released in reverse order: close the reader, dispose the command, dispose the transaction, and finally dispose the connection. Explicitly commit successful work before leaving the transaction scope. This structure sends success, SQL failure, cancellation, and mapping failure through one cleanup path instead of relying on scattered Close calls that execute only after successful statements.

The main benefit of using and await using is not shorter syntax. The scope records ownership in a form reviewers and analyzers can inspect. A reader cannot legitimately survive the command that created it, and a transaction cannot survive its connection. If a method returns a deferred enumerable that reads rows only when the caller iterates, the lifetime becomes ambiguous: either the original scope has already ended, or the live database objects must leak into the caller. For SQLite access, materialize ordinary data transfer objects while the reader is open, then return those detached values.

A transaction must also have one completion owner. The success path commits; failure and cancellation paths roll back explicitly or through documented disposal behavior. An inner repository should not begin a transaction and expect an outer service to guess when it should commit. If several operations must be atomic, the outer unit of work creates the connection and transaction and passes them explicitly. If they are unrelated, keeping them in one large transaction only prolongs the single-writer interval.

Commands and parameter collections are part of the same rule. Reassigning CommandText does not prove that earlier parameters, a prior transaction association, or reader state have been reset safely. A short-lived command is normally much cheaper than diagnosing cross-worker mutable state. Reuse can be reasonable inside one connection and one transaction for a repeated parameterized insert, but the command remains private to that operation and is disposed before the transaction.

A method returning successfully is not direct evidence that the operating system released the database files. On Windows, an open handle commonly prevents a test database or its sidecar files from being removed or renamed. A focused resource test should let all readers, commands, transactions, and connections leave scope, clear provider pools when the test intentionally uses them, and then delete the temporary directory containing the database, WAL, and shared-memory files. Successful deletion is not a complete concurrency proof, but it is stronger evidence of release than a manually maintained Boolean flag.

Disposal order matters on exceptional paths as well. Suppose mapping the tenth row throws because a projected column is missing. Without nested scopes, code after the loop may never close the reader. With a nested await using, the reader and command are released during stack unwinding, the transaction can roll back, and the connection is finally disposed. The original mapping failure remains observable instead of being followed by a second, unrelated locking problem caused by leaked resources.

Understand what WAL and busy timeout do not guarantee

WAL improves overlap between readers and a writer, while a busy timeout waits for short lock conflicts; neither feature permits multiple write transactions to commit simultaneously. SQLite still allows only one active writer, so applications must shorten transactions, index relevant queries, and bound write concurrency before treating timeout as a final safety margin.

SQLite’s official WAL documentation explains that readers generally do not block a writer and a writer generally does not block readers in WAL mode. That is a valuable capability, but the same documentation also describes cases in which WAL operations can still return SQLITE_BUSY. Checkpoints, long-lived snapshots, attempts to upgrade a read transaction, and competing writers still create boundaries. An application that treats WAL as a promise of lock-free operation will misclassify expected contention as an unexplained provider defect.

The official SQLite transaction documentation is even more direct: separate connections may hold multiple read transactions, but only one write transaction exists at a time. Creating a thousand write tasks does not create a thousand independent write lanes. It creates a large queue of contenders, plus scheduling overhead and more cancellation and timeout states to reason about. A bounded channel, a semaphore, or a dedicated writer queue can provide backpressure before work reaches the provider.

Timeout values require an explicit latency budget. Zero has different meanings at different layers: native SQLite busy timeout disables its busy handler at zero, whereas Microsoft.Data.Sqlite CommandTimeout=0 means no timeout. Do not use the latter as an immediate-failure setting. An extremely large timeout may make a request appear hung and consume its entire service-level budget. The value should be long enough for an expected short transaction, yet bounded so callers receive a controlled failure. Retry counts, total elapsed time, and the final SQLite error code should be observable. Retry loops must never be infinite and should not repeat a non-idempotent operation after an uncertain commit.

PRAGMA journal_mode=WAL must be checked by reading its result, not assumed because the command was issued. Journal mode is database state. This lab sets DefaultTimeout=5 on every connection, giving provider commands a five-second retry timeout. That provider contract is distinct from native PRAGMA busy_timeout, measured in milliseconds; the reproduction does not mix the two mechanisms. Provider names matter: a connection string copied from System.Data.SQLite is not automatically valid for Microsoft.Data.Sqlite, and defaults may differ. State the provider and fixed package version in both documentation and tests.

The best way to reduce lock pressure is often to do less while holding the writer. Perform pure computation before beginning the transaction. Use a parameterized command and a transaction for a coherent batch rather than committing every row. Add indexes for predicates that must find rows before an update. Do not perform web requests, large file writes, user interaction, or unrelated logging I/O while a database reader or transaction remains open. WAL and timeout are guardrails around disciplined work, not substitutes for it.

Map DataReader columns and SQLite INTEGER values deliberately

A robust mapper resolves available column ordinals before reading values and follows SQLite’s actual storage classes instead of assuming every projection returns a full entity or every INTEGER fits GetInt32. This converts aliases, optional columns, nulls, and 64-bit values into explicit contracts rather than timing-dependent exceptions inside an active reader.

Microsoft’s ADO.NET DataReader documentation describes a forward-only, unbuffered stream and explicitly instructs callers to close the reader when finished. That lifetime is part of the connection’s active work. If a reflective mapper requests a property name that a projection did not select, it may throw midway through enumeration. A properly nested reader is still disposed; a helper that returned the reader may leave cleanup to a caller that never expected this failure.

Build a case policy and a map from returned column names to ordinals once per result shape. Required fields should fail immediately with a clear contract message. Fields explicitly documented as optional may be skipped when absent. Each nullable value should be tested with IsDBNull before a typed accessor is used. This is more predictable than catching IndexOutOfRangeException for every property or silently assigning defaults that erase the difference between absent, null, and zero.

SQLite’s INTEGER storage class supports signed values up to 64 bits. Microsoft.Data.Sqlite’s official type documentation explains that provider values ultimately map through SQLite’s primitive storage types. Identifiers, counts, and integer time values should therefore be read with GetInt64 or converted to Int64, then narrowed only at a boundary that performs a range check. Calling GetInt32 may appear correct with tiny fixtures and truncate or fail only after an identifier or count grows beyond the 32-bit range. Do not assume the accessor performs a checked narrowing conversion.

Avoid using SELECT * as the mapper contract. Name the projected columns and aliases so schema review can see exactly what the reader expects. A generic mapper should not make all missing columns optional merely to avoid failures; that can create partially populated objects whose errors surface much later. Distinguish a strict projection from an intentionally extensible projection, and test both missing-field and extra-field behavior.

The isolated lab in this article reads COUNT(*) and MAX(id) by ordinal and calls GetInt64. That test is intentionally narrow. It proves the aggregate path used by the lab does not truncate the values and that its reader leaves scope before cleanup. A project-level test suite should additionally cover null values, a value above the Int32 maximum, changed aliases, missing required fields, optional fields, and a mapper exception during enumeration.

Design helpers to return data, not live provider resources

An ADO.NET helper should return materialized values or a controlled callback result, not a live connection, transaction, command, or bare DataReader. The component that creates a disposable resource can then release it deterministically, and callers cannot store that resource in UI state, a background task, or another thread.

A GetReader() helper is a common ownership trap. The helper creates a connection and command, returns the reader, and loses reliable knowledge of when enumeration ends. CommandBehavior.CloseConnection can couple reader closure to connection closure, but it does not by itself make command, transaction, cancellation, and error ownership obvious. For ordinary result sizes, materialize a list within the helper. When true streaming is required, expose a callback that runs inside the known reader scope or define a dedicated disposable abstraction with documented ownership.

A write helper can accept data and a cancellation token, open its own connection, begin a transaction, construct a parameterized command, commit, and leave scope. When multiple repositories must share one transaction, use an explicit unit-of-work object owned by the outer service. Pass the connection and transaction to participating methods. Do not use an ambient static “current transaction,” because its meaning becomes undefined when asynchronous operations overlap.

Asynchronous method names do not create additional SQLite writer capacity. Microsoft documents that its SQLite async methods execute synchronously. Consequently, LINQ Select(async ...) followed by Task.WhenAll does not establish overlapping workers. The corrected lab below uses four dedicated threads and a barrier with a fifteen-second timeout to establish overlap before transactions begin. The ownership constraints remain identical in synchronous and asynchronous code: one worker uses one object graph at a time, the writer transaction is short, and every disposable has a visible scope.

A testable helper accepts a connection string or factory through configuration, while its tests supply only a generated temporary path and synthetic values. Tests must not contain an existing database location, copied application rows, internal hostnames, or historical logs. The artifact should state whether pooling is enabled because that affects deletion tests. This article’s lab sets Pooling=false so file-lifetime behavior is direct, then still clears all pools before deleting as a defensive, observable final step.

Connection pooling does not change logical ownership. Disposal may return a connection to a pool rather than closing the native handle immediately, but application code must behave as if its right to use the object ended. Tests that require physical file deletion can disable pooling or explicitly clear pools after every logical connection has been disposed. A failure to delete then becomes useful evidence that something still owns a resource.

Verification and reproduction with a fixed-version isolated lab

A safe reproduction fixes the SDK and provider version, creates a new WAL database in a temporary directory, writes through several independent connections, verifies exact totals, and finally deletes the directory. Its pass conditions cover journal mode, data integrity, zero exceptions, non-shared objects, typed reads, and resource release rather than merely observing that the process stayed alive.

Create an empty net10.0 console project and pin Microsoft.Data.Sqlite 10.0.0. Confirm dotnet --info reports SDK 10.0.400, and verify that the project has no route to an existing application database.
Generate a temporary directory and lab.db. Set Pooling=false and DefaultTimeout=5, open a setup connection, query the SQLite library version, execute PRAGMA journal_mode=WAL, and require the journal-mode result to equal wal.
Start four dedicated worker threads with LongRunning tasks and require all to pass the barrier within fifteen seconds. Each worker creates its own connection and transaction and inserts twenty-five synthetic rows with a parameterized command. No connection, command, transaction, or reader is placed in shared state.
Await every worker, open a separate verification connection, execute SELECT COUNT(*), MAX(id) FROM items, and read both fields with GetInt64. Require both values to equal one hundred and the exception queue to remain empty.
Let every nested disposable leave scope, call SqliteConnection.ClearAllPools(), delete the temporary directory recursively, and require the directory no longer to exist.

The following is the complete Program.cs, not a standalone-looking insert excerpt with undefined variables. In an empty directory, save this project as SqliteConnectionLab.csproj. The earlier fixture used a fixed temporary directory and did not establish worker overlap; this corrected fixture creates a unique directory and never deletes a pre-existing directory.


  
    Exe
    net10.0
    enable
    enable

using Microsoft.Data.Sqlite;
using System.Collections.Concurrent;

// Synthetic data only. This run owns only its uniquely generated directory.
var root = Directory.CreateTempSubdirectory("sqlite-lifetime-qa-").FullName;
var database = Path.Combine(root, "lab.db");
var cs = new SqliteConnectionStringBuilder
{
    DataSource = database,
    Pooling = false,
    DefaultTimeout = 5
}.ToString();
try
{
    using (var setup = new SqliteConnection(cs))
    {
        setup.Open();
        using var cmd = setup.CreateCommand();
        cmd.CommandText = "SELECT sqlite_version();";
        var version = Convert.ToString(cmd.ExecuteScalar());
        cmd.CommandText = "PRAGMA journal_mode=WAL;";
        var mode = Convert.ToString(cmd.ExecuteScalar());
        Console.WriteLine($"sqlite_version={version}");
        Console.WriteLine($"journal_mode={mode}");
        if (version != "3.49.1" || mode != "wal")
            throw new InvalidOperationException("Fixed-version or WAL assertion failed.");
        cmd.CommandText = "CREATE TABLE items(id INTEGER PRIMARY KEY, worker INTEGER NOT NULL, value TEXT NOT NULL);";
        cmd.ExecuteNonQuery();
    }

    var threadIds = new ConcurrentDictionary();
    using var ready = new Barrier(4);
    var tasks = Enumerable.Range(0, 4).Select(worker => Task.Factory.StartNew(() =>
    {
        using var connection = new SqliteConnection(cs);
        connection.Open();
        threadIds.TryAdd(Environment.CurrentManagedThreadId, 0);
        if (!ready.SignalAndWait(TimeSpan.FromSeconds(15)))
            throw new TimeoutException("Worker start barrier timed out.");
        using var transaction = connection.BeginTransaction();
        for (var item = 0; item < 25; item++)
        {
            using var command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "INSERT INTO items(worker,value) VALUES ($worker,$value);";
            command.Parameters.AddWithValue("$worker", worker);
            command.Parameters.AddWithValue("$value", $"value-{worker}-{item}");
            command.ExecuteNonQuery();
        }
        transaction.Commit();
    }, CancellationToken.None, TaskCreationOptions.LongRunning, TaskScheduler.Default)).ToArray();
    Task.WaitAll(tasks);

    long count;
    long maxId;
    using (var verify = new SqliteConnection(cs))
    {
        verify.Open();
        using var command = verify.CreateCommand();
        command.CommandText = "SELECT COUNT(*), MAX(id) FROM items;";
        using var reader = command.ExecuteReader();
        if (!reader.Read()) throw new InvalidOperationException("Missing aggregate row.");
        count = reader.GetInt64(0);
        maxId = reader.GetInt64(1);
    }
    if (count != 100 || maxId != 100 || threadIds.Count != 4)
        throw new InvalidOperationException("Count or worker thread assertion failed.");
    Console.WriteLine("workers=4");
    Console.WriteLine($"worker_threads={threadIds.Count}");
    Console.WriteLine($"rows={count}");
    Console.WriteLine($"max_id={maxId}");
    Console.WriteLine("errors=0");
    // Ownership is established by code review: all provider objects are worker-local.
    Console.WriteLine("connections_shared=False");
}
finally
{
    SqliteConnection.ClearAllPools();
    Directory.Delete(root, recursive: true);
    var deleted = !Directory.Exists(root);
    Console.WriteLine($"temp_deleted={deleted}");
    if (!deleted) throw new IOException("Temporary cleanup failed.");
}

Run:

dotnet restore
dotnet build --configuration Release --no-restore
dotnet run --configuration Release --no-build

Expected output (any assertion or cleanup failure produces a nonzero exit code):

sqlite_version=3.49.1
journal_mode=wal
workers=4
worker_threads=4
rows=100
max_id=100
errors=0
connections_shared=False
temp_deleted=True

connections_shared=False reflects structural code review, not a dynamic race detector. Four distinct threads and the barrier establish overlap at the starting point, not simultaneous SQLite write commits. The fixture does not cover cancellation, mapper exceptions, failed-transaction rollback, or integers above the Int32 maximum.

During independent review, the complete corrected lab below was run three times on an isolated Windows 11 host with dotnet restore, a Release build, and dotnet run --configuration Release --no-build. The fixed environment was .NET SDK 10.0.400 with Microsoft.Data.Sqlite 10.0.0 and SQLite 3.49.1. The process reported journal_mode=wal, workers=4, worker_threads=4, rows=100, max_id=100, errors=0, connections_shared=False, and temp_deleted=True, then exited with code zero.

Restore also emitted NU1903 for a known high-severity advisory affecting the transitive SQLitePCLRaw.lib.e_sqlite3 2.1.11 package. That warning is retained rather than hidden. The run proves only the non-destructive resource-lifetime assertions described here. It is not dependency-security approval, a recommendation to deploy that transitive version, or evidence that a particular workload will meet latency and capacity goals.

The lab also does not prove that four writers execute at the same instant. SQLite serializes write transactions. It shows that four tasks with independent ADO.NET object graphs completed under a bounded timeout, produced an exact total, performed 64-bit aggregate reads, and released the temporary files. A capacity study would require representative data, a controlled load profile, latency percentiles, checkpoint observations, failure injection, and a documented retry policy.

Release checklist and architectural decision

Before release, review object ownership, disposal order, transaction duration, reader closure, integer mapping, verified WAL mode, bounded timeout, write backpressure, and physical temporary-file cleanup together. Omitting any one can let a green functional test conceal a long reader, shared object, truncated value, unbounded wait, or unreleased file handle.

Every concurrent worker creates and disposes its own connection; no singleton stores an open provider connection.
Transaction, command, and reader objects use nested using or await using, including exception and cancellation paths.
Readers close before their connection, and helpers do not return bare readers or live transactions.
The application checks the returned journal mode and gives busy timeout an explicit latency and failure budget.
Writer transactions contain only required database operations; computation, file work, and remote calls occur outside them.
SQLite INTEGER values are read as Int64 first, and narrowing conversions include range validation.
A bounded queue, semaphore, or equivalent policy prevents an uncontrolled number of write contenders.
Tests use synthetic data and temporary paths, then actually remove the database, WAL, SHM, and directory.
Restore and build warnings remain visible and receive a separate dependency-risk decision.

This subject overlaps with an earlier article about SQLite WAL, busy timeout, and transaction boundaries, but the search intent is narrower and operationally different. This article answers ADO.NET connection ownership, nested disposal order, DataReader lifetime, Int64 mapping, and Windows deletion proof. It does not explain EF Core Migration diff review and does not use MySQL INFORMATION_SCHEMA for DDL acceptance. Readers facing schema change should use a migration workflow; readers facing shared connections, open readers, or undeletable SQLite test files should use this lifecycle workflow.

The resulting architecture is intentionally modest: bounded producers, one private ADO.NET object graph per unit of work, short transactions, materialized results, deliberate mapping, and observable cleanup. Those constraints do not remove SQLite’s single-writer model. They make that model explicit enough to test, monitor, and change when measured workload requirements eventually exceed it.

Official references

These official references support provider thread-safety limits, transaction behavior, type mappings, and reader closure. The fixed-version experiment supplies functional evidence only; it is not approval of dependency security or production capacity.