SQLite ADO.NET 連線生命週期：所有權、釋放順序與驗證

ADO.NET 連線生命週期不明確時，SQLite 的 database is locked 常被直接歸因於資料庫能力不足，接著便加入更多重試、放大 timeout，甚至把所有工作塞進一把全域鎖。然而在 ADO.NET 應用程式裡，真正需要先回答的是：誰建立連線、誰擁有命令與 reader、交易在何處結束，以及例外路徑是否確實釋放最內層資源。未完成的 reader、懸掛的交易或被多執行緒共用的 provider 物件，都會把原本短暫的檔案占用延長成難以判讀的競爭。

本文聚焦「連線與其子資源的生命週期契約」，而不是再寫一篇 WAL 調校或 EF Core Migration 指南。WAL 與 busy timeout 仍是背景條件，但它們不能替代所有權設計。範例使用 Microsoft.Data.Sqlite 10.0.0、.NET SDK 10.0.400 與隔離暫存資料庫；不連接網站、共用磁碟或任何既有資料，也不把單次測試結果誤寫成正式負載的效能保證。

核心答案：每個工作單元應擁有自己的連線

ADO.NET 連線生命週期管理的核心，是讓每個工作單元建立並釋放自己的 connection、transaction、command 與 reader，禁止多個執行緒同時使用同一個 provider 物件。 WAL 讓讀取與寫入較能並行，timeout 讓短暫競爭有等待空間，明確且有界的資源範圍則負責結束邏輯所有權；實體檔案 handle 是否關閉，還取決於 pooling 與其他連線。

「一個工作單元一條連線」不是要求每個 SQL 敘述都建立永久的新物件，也不是否定 provider 的連線池。它是在程式碼層建立可推理的所有權邊界：方法進入時取得連線，完成交易與讀取後依反向順序釋放，方法離開時不把仍可操作的 reader 或 transaction 暴露給外部。是否啟用 pooling 是另一個決策；物件被 Dispose 後可能回到池中，但呼叫端已失去使用權，這個契約不可因實作細節而改變。

若把一個開啟的 connection 放進 singleton helper，再由多個 Task 同時使用，問題不只是一個 SQL 指令撞到另一個指令。交易狀態、目前 reader、參數集合、錯誤處理與關閉時機都會互相交錯。Microsoft 的 SQLite provider 文件明確指出 SqliteConnection、SqliteCommand 與 SqliteDataReader 不可被多個執行緒並行共用。最可靠的修法不是在 helper 周圍補零散的 lock，而是讓 helper 接受工作函式或 connection factory，並把物件生命週期留在單一呼叫範圍內。

這個規則也讓錯誤定位變得直接。若每個工作都有自己的連線，可以把 worker 編號、交易開始與結束、嘗試次數與耗時記錄在同一個觀測範圍；若連線是全域共享，任何一條紀錄都可能描述另一個工作留下的狀態。設計的第一個驗收條件因此不是「沒有看到 locked」，而是「任何工作都無法取得另一個工作的 ADO.NET 物件參考」。

連線、交易、命令與 reader 的釋放順序

資源應依建立順序巢狀配置，並在離開範圍時反向釋放：先關 reader，再釋放 command，接著釋放 transaction，最後釋放 connection；交易成功時的 Commit 應在交易作用域結束前明確呼叫。 這個結構讓正常完成、SQL 例外、取消與資料對映失敗都走同一條清理路徑，不需要依賴散落的 Close 呼叫或成功分支。

await using 或 using 的價值，不只是少寫一行 Dispose。它把所有權嵌入語法範圍，使 code review 可以從縮排看出 reader 不會活過 command、transaction 不會活過 connection。若方法回傳延遲列舉，卻在列舉時才讀取 reader，表面上的 using 可能已離開或反過來被迫延長到呼叫端；兩種情形都使所有權模糊。對 SQLite 這類檔案式資料庫，應在資源範圍內完成 materialize，再回傳純資料物件。

交易要有唯一的完成責任。成功路徑呼叫 Commit，失敗或取消路徑讓 Dispose 或明確 Rollback 收尾；不要由內層 repository 開始交易，再期待外層 service 猜測何時提交。若一批寫入原本是同一個業務單位，逐筆開關交易會反覆取得 writer，增加競爭視窗與磁碟同步成本；但把不相關的大量工作塞進一個超長交易，同樣會讓其他 writer 等待。邊界應對齊資料一致性需求，而不是對齊迴圈寫法。

命令與參數也不應跨工作重用。重設 CommandText 並不代表舊參數、transaction 關聯與 reader 狀態都安全清空。建立短生命週期 command 的成本通常遠低於診斷共享狀態錯誤的成本。若需要重複執行同一個參數化 INSERT，可以在單一 connection 與單一 transaction 內重用該 command，但不能把它交給另一個 worker。

最後，Dispose 的可觀測證據不應只靠「方法回傳了」。在 Windows 上，未釋放的檔案 handle 常會使測試資料庫無法刪除或重新命名。隔離測試應在所有 ADO.NET 物件離開範圍後清除 pool，再實際刪除包含 .db、-wal 與 -shm 的暫存目錄。刪除成功不是完整的併發證明，卻是比布林旗標更直接的資源釋放驗收。

WAL 與 busy timeout 的責任邊界

WAL 的責任是改善 reader 與 writer 的並行，busy timeout 的責任是等待短暫鎖競爭；兩者都不會把多個 writer 變成真正同時提交。 SQLite 官方交易文件仍限定同一時間只有一個 write transaction，因此應先縮短交易、建立必要索引並限制並行度，再以 timeout 吸收合理抖動。

SQLite 官方 WAL 文件說明，WAL 模式下 reader 通常不阻塞 writer，writer 也通常不阻塞 reader；這是一個重要改善，但不是「永遠不會 SQLITE_BUSY」。checkpoint、交易升級、長時間 reader 與另一個 writer 仍可能形成等待。若把 timeout 設得很大，程式可能只是更久才失敗；必須區分兩種零值：SQLite 原生 busy timeout 的零值會停用 busy handler，但本例 Microsoft.Data.Sqlite 的 CommandTimeout=0 表示不設逾時上限，不能拿來達成立即失敗。timeout 應根據工作延遲預算設定，並配合可量測的重試與失敗上限。

PRAGMA journal_mode=WAL 是資料庫層狀態，應在可控的初始化階段確認實際回傳值，而不是只相信送出的字串。本例在每條連線以 DefaultTimeout=5 設定 provider 命令逾時（秒），由 provider 重試 busy／locked；這與原生 PRAGMA busy_timeout（毫秒）不是相同契約，範例不混用兩套設定。不同 provider 對關鍵字、預設值與重試行為可能不同，不能把 System.Data.SQLite 的範例原封不動套到 Microsoft.Data.Sqlite。文章與測試都應明確標示 provider 與版本。

降低競爭最有效的手段往往是減少鎖持有時間。把查詢需要的欄位建立索引、把批次寫入放在一個適當大小的交易、在進入交易前完成純計算，以及避免在 reader 開啟時執行網路或檔案工作，都比無限制增加 timeout 更能改善可預測性。WAL 與 timeout 是防護欄，不是容量規劃，也不是忽略資源生命週期的許可證。

對高峰流量應建立背壓。若應用程式瞬間產生上千個寫入 Task，即使每個 Task 都有獨立 connection，最終仍會排隊爭取單一 writer。可用有界通道、SemaphoreSlim 或專用寫入佇列限制同時嘗試寫入的數量。這個排程層與 ADO.NET 所有權互補：前者控制有多少工作進場，後者保證每個進場工作不洩漏自己的資源。

DataReader 對映要尊重欄位與 SQLite 儲存型別

reader 對映應先解析可用欄位 ordinal，再以 SQLite 實際儲存型別讀值，不能假設每個查詢都回傳完整實體欄位或 INTEGER 必然適合 GetInt32。 這能把投影差異、別名與 64 位元整數轉成明確的契約，而不是只在特定資料出現時才發生例外。

Microsoft 的 ADO.NET 文件把 DataReader 描述為未緩衝、循序讀取的資料流，並要求完成後關閉。這代表 reader 的生命週期就是 connection 被占用的一部分。映射器若反覆用不存在的欄位名稱取值，會在讀取途中拋出例外；若例外路徑沒有巢狀 using，reader 與 command 的釋放便可能延後。穩健做法是在第一列之前建立欄位名稱到 ordinal 的大小寫規則一致對照表，只有查詢真的提供欄位時才映射。

SQLite 的 INTEGER 是最多 64 位元的整數儲存類別。Microsoft.Data.Sqlite 的型別文件也把 SQLite 基本型別界線說清楚。主鍵、計數與時間刻度應優先以 GetInt64 或 Convert.ToInt64 讀取，再在有範圍檢查的地方轉成較小型別。直接用 GetInt32 可能在小型測試資料上一直成功，直到數值超過 32 位元範圍才出現截斷或轉換失敗等問題，不能假設 accessor 一定替你做範圍檢查；這種錯誤與併發無直接關係，卻常在同一批資料存取 helper 中一起爆發。

不要讓通用反射映射器掩蓋查詢契約。每個 SELECT 應明確列出欄位，不使用 SELECT *，映射器應對必填欄位缺失立即失敗，對明確標記為可選的欄位才允許跳過。對 NULL 先呼叫 IsDBNull，再選擇 nullable 型別或預設策略。這些規則讓資料問題在 reader 範圍內被捕捉、記錄並釋放，而不是帶著半成品物件離開連線範圍。

本文的隔離測試使用 COUNT(*) 與 MAX(id)，透過欄位 ordinal 和 GetInt64 回讀。它不是一般映射器的完整測試，但能驗證兩個關鍵點：SQLite 聚合整數以 64 位元方式讀取，以及 reader 在刪除暫存目錄之前確實離開作用域。正式專案還應加入欄位缺失、NULL、超出 Int32 範圍與別名變更的回歸案例。

Helper API 應暴露資料，不應暴露活著的資源

安全的 ADO.NET helper 應回傳已完成 materialize 的資料或受控 callback 結果，而不是開啟中的 connection、transaction、command 或 reader。 這樣建立者同時是釋放者，呼叫端無法把資料庫資源保存到背景 Task、UI 事件或另一條執行緒。

常見反模式是 GetReader()：helper 建立 connection 和 command，回傳 reader，卻沒有方法可靠知道呼叫端何時讀完。即使使用 CommandBehavior.CloseConnection，command、transaction 與錯誤處理責任仍容易被拆散。較小資料集可在 helper 內建立清單後回傳；需要串流時，應提供 callback，讓 callback 在明確的 reader 範圍內執行，或建立具有文件化 Dispose 責任的專用物件，而不是回傳裸 reader。

寫入 helper 也應接受資料與取消權杖，在內部建立 connection、開始 transaction、建立參數化 command、提交，最後離開範圍。若上層需要讓數個 repository 操作共享同一交易，應把它設計成明確的 unit-of-work 物件，由最外層建立與釋放；不要讓任意方法偷偷抓取全域 current transaction。明確傳遞依賴雖然較多字，卻能被編譯器、測試與 code review 追蹤。

非同步 API 也不能假設自動增加 SQLite 的平行能力。Microsoft 官方非同步限制明確指出，這些非同步方法實際上同步執行。只用 LINQ Select(async ...) 搭配 Task.WhenAll 並不能證明工作重疊；以下修正版以四條專用執行緒及最長十五秒的 Barrier 等待，驗證工作已同時到達交易前的起跑點。無論同步或非同步，禁止並行使用同一物件、縮短 writer 交易與確實 Dispose 的規則不變。若工作主要受單一 writer 限制，盲目建立更多 Task 只會增加排程與競爭。

可測試的 helper 應讓 connection string 或 factory 由組態注入，但測試只提供暫存路徑與合成資料。驗證完成後清除資料庫與 sidecar 檔，不留下工作目錄污染。正式連線值、使用者資料、內部路徑與歷史 log 不應出現在文章、測試輸出或驗證清單中。

驗證與重現：固定版本的隔離連線測試

安全重現應固定 SDK 與 provider 版本，在全新暫存目錄建立 WAL 資料庫，以多個獨立 connection 寫入後精確回讀筆數，最後刪除整個目錄。 通過條件同時涵蓋模式、資料完整性、零例外、禁止共用物件與資源釋放，不把「程式沒有當掉」當成充分證據。

1. 建立空白 net10.0 Console 專案，固定 Microsoft.Data.Sqlite 10.0.0；確認 dotnet --info 顯示 SDK 10.0.400，且專案不連接任何既有資料來源。
2. 在暫存目錄建立 lab.db，將 Pooling 設為 false 以直接觀察生命週期，使用 DefaultTimeout=5 設定 provider 等待上限，開啟連線後查詢 SQLite 版本並執行 PRAGMA journal_mode=WAL，並確認 journal mode 實際回傳 wal。
3. 以 LongRunning Task 啟動四個專用執行緒，在十五秒內通過 Barrier 後執行交易；每個 worker 自行建立 connection 與 transaction，以參數化命令寫入二十五列。不得把 connection、command、transaction 或 reader 放在共用集合。
4. 等待全部 worker 結束，再以新的 connection 查詢 COUNT(*) 與 MAX(id)，透過 GetInt64 讀取；要求兩者皆為一百且例外佇列為零。
5. 所有 reader、command、transaction 與 connection 離開範圍後呼叫 SqliteConnection.ClearAllPools()，刪除暫存目錄，並要求目錄確實不存在。

以下是完整 Program.cs，不是可直接執行的單筆 INSERT 摘錄。建立空白目錄，將專案檔命名為 SqliteConnectionLab.csproj，並存入下列內容。舊 fixture 使用固定暫存目錄且未證明工作重疊；以下修正版每次產生唯一目錄，不會刪除既有目錄。

  
    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.");
}

執行命令：

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

預期輸出（任一斷言或清理失敗，程序結束碼皆不為零）：

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 來自程式結構審查，不是動態競爭偵測器；四條不同執行緒與 Barrier 證明起跑點重疊，不保證 SQLite 寫入同時提交。測試未涵蓋取消、對映例外、失敗交易回滾或高於 Int32 上限的值。

獨立審查時以本文完整程式重跑三次；本次在 Windows 11 隔離環境實際執行 dotnet restore、Release build 與 dotnet run --no-build。結果為 .NET SDK 10.0.400、Microsoft.Data.Sqlite 10.0.0、SQLite 3.49.1；輸出 journal_mode=wal、workers=4、worker_threads=4、rows=100、max_id=100、errors=0、connections_shared=False、temp_deleted=True，程序結束碼為零。Restore 同時回報相依套件 SQLitePCLRaw.lib.e_sqlite3 2.1.11 的 NU1903 已知弱點警告；因此這次結果只證明本文的非破壞性生命週期測試，並不構成相依安全核准或正式版本選型建議。

這個 lab 不證明所有硬體、檔案系統或負載都能支撐四個 writer，也不提供吞吐量基準。它證明的是一組較窄的事實：固定測試條件下，四個 worker 未共用 ADO.NET 物件，寫入總數與主鍵上限符合預期，reader 以 Int64 回讀，所有資源釋放後暫存目錄可刪除。若要評估正式容量，還需要代表性資料量、延遲分布、checkpoint 觀測與受控壓力測試。

發布前檢查清單與架構判斷

發布前應同時檢查物件所有權、交易長度、reader 關閉、型別映射、WAL 實際模式、timeout 上限、寫入背壓與暫存檔刪除。 其中任何一項缺失，都可能讓一次成功測試掩蓋長時間 reader、共享物件、資料截斷或無限等待。

• 每個並行工作自行建立並釋放 connection，沒有 singleton 開啟連線。
• transaction、command、reader 都在巢狀 using 或 await using 中，例外與取消路徑也會清理。
• reader 在 connection 之前關閉，helper 不回傳裸 reader 或仍活著的 transaction。
• WAL 以查詢回傳值確認，busy timeout 有明確延遲預算與失敗上限。
• 寫入交易只包含必要資料庫操作，計算、網路與檔案工作移到交易外。
• SQLite INTEGER 先以 Int64 讀取，縮小轉型有範圍檢查；可選欄位與必填欄位分開處理。
• 多 writer 有有界併發或排隊策略，沒有把增加 Task 數量當成吞吐量保證。
• 測試使用合成資料與暫存路徑，最後實際刪除資料庫、WAL、SHM 與目錄。
• restore/build 的警告被保留並另行評估，功能測試通過不等同相依套件安全核准。

這個主題與既有 SQLite WAL、busy timeout 與交易邊界文章有交集，但搜尋意圖不同：本文回答的是 ADO.NET connection ownership、Dispose 順序、DataReader 生命週期與 Windows 檔案釋放證據。它也不討論 EF Core Migration 差異審查，亦不使用 MySQL INFORMATION_SCHEMA 驗收。若讀者面對的是 schema 變更，應查閱 migration 流程；若面對的是連線被共用、reader 未關閉或暫存檔無法刪除，才適用本文方法。

官方參考資料

以下官方文件分別支持 provider 執行緒限制、交易模型、SQLite 型別與 reader 關閉責任。版本固定測試只是功能證據，不代表相依套件安全或正式容量已獲核准。

• https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/database-errors
• https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/transactions
• https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/types
• https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader
• https://www.sqlite.org/wal.html