0

在部落格 Log 發現 SQLite 在執行更新時偶發資料庫鎖定錯誤：

Oct 21 05:19:46 Blog darkblog-core: An unhandled exception has occurred while executing the request.
Oct 21 05:19:46 Blog darkblog-core: Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
Oct 21 05:19:46 Blog darkblog-core: at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
Oct 21 05:19:46 Blog darkblog-core: at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
Oct 21 05:19:46 Blog darkblog-core: at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
Oct 21 05:19:46 Blog darkblog-core: at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action`2 paramReader) in C:\projects\dapper\Dapper\SqlMapper.cs:line 2827
Oct 21 05:19:46 Blog darkblog-core: at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in C:\projects\dapper\Dapper\SqlMapper.cs:line 570
Oct 21 05:19:46 Blog darkblog-core: at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 443

依先前印象，SQLite 本身為 Thread-Safe，可在多執行緒環境使用，因此我在寫程式時就沒另加 lock 保護，但實測結果顯然與預期不同。

官方文件 Using SQLite In Multi-Threaded Applications 提到 SQLite 有三種模式：

  1. Single-Thread
    停用所有 Mutexes 防護，完全不支援多執行緒
  2. Multi-Thread
    可以多緒執行，但每條連線不能同時被兩條以上執行緒存取
  3. Serialized
    在此模式下，SQLite 完全 Thread-Safe，使用上沒有任何限制

執行模式可在編譯、啟動或執行時決定，預設為 Serialized。Microsoft.Data.Sqlite NuGet Package 內附的 SQLite 模式為 Serialized。參考 而在 Linux 環境，使用的 SQLite 程式庫隨系統安裝，依據 SQLite FAQ：

SQLite is threadsafe. We make this concession since many users choose to ignore the advice given in the previous paragraph. But in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1. Both the Windows and Linux precompiled binaries in the distribution are compiled this way.

由此可推測假設 Windows 與 Linux 上，SQLite 都被設成 Serialized，是 Thread-Safe 的。

我設計了以下 .NET Core 測試程式：(Blog 資料庫來自官方範例、Dapper 做法請參考：ASP.NET Core 練功筆記 2 – Ubuntu + SQLite + Dapper )

using Dapper;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Diagnostics;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace SqliteTest
{
    class Program
    {
        static void Init(int n)
        {
            using (var ctx = new BloggingContext())
            {
                if (!ctx.Blogs.Any())
                {
                    for (var i = 1; i<=n; i++)
                    ctx.Blogs.Add(new Blog()
                    {
                        BlogId = i,
                        Url = "http://blog.darkthread.net"
                    });
                    ctx.SaveChanges();
                }
            }
        }

        const int THREAD_COUNT = 8;
        const int TIMES = 1000;
        static int count = 0;

        static void Main(string[] args)
        {
            Init(THREAD_COUNT);
            var sw = new Stopwatch();
            sw.Start();
            for (var i = 1; i <= THREAD_COUNT; i++)
            {
                var blogId = i;
                var t = Task.Factory.StartNew(() =>
                {
                    RunTest(blogId);
                });
            }
            var totalCount = THREAD_COUNT * TIMES;
         
            while (count < totalCount)
            {
                Thread.Sleep(1000);
                Console.CursorLeft = 0;
                Console.Write($"{count:n0} ({count * 1.0 / totalCount:p2})");
            }
            sw.Stop();
            Console.WriteLine();
            Console.WriteLine($"Done {sw.ElapsedMilliseconds:n0} ms");
            Console.ReadLine();

        }

        static void RunTest(int blogId)
        {
            using (var ctx = new BloggingContext())
            {
                var cn = ctx.Database.GetDbConnection();
                for (var i = 0; i < TIMES; i++)
                {
                    try
                    {
                        cn.Execute("UPDATE Blogs SET Url = @u WHERE BlogId = @i",
                            new { u = Guid.NewGuid().ToString(), i = blogId });
                        Interlocked.Increment(ref count);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }

    }
}

我用 8 條 Thread 同時各跑 1000 次 UPDATE 動作，在 Windows 環境，8000 次更新完美跑完沒出現錯誤。

同程式移到 CentOS 下執行，8000 次更新噴出 7 次 database is locked 錯誤，如同我在 Blog 遇到的狀況。

修改程式，加上 lock 保護：

        static object syncer = new object();

        static void RunTest(int blogId)
        {
            using (var ctx = new BloggingContext())
            {
                var cn = ctx.Database.GetDbConnection();
                for (var i = 0; i < TIMES; i++)
                {
                    try
                    {
                        lock (syncer)
                        {
                            cn.Execute("UPDATE Blogs SET Url = @u WHERE BlogId = @i",
                                new { u = Guid.NewGuid().ToString(), i = blogId });
                        }
                        Interlocked.Increment(ref count);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }

在 Windows 執行如預期順利跑完，時間也沒有變慢。

移師 CentOS 也成功跑完，執行速度與 Windows 時間差不多。

接著來個瘋狂實驗，如果讓兩條 Thread 共用一條 Connection 呢？

答案是會噴出 System.AccessViolationException: 'Attempted to read or write protected memory. This is often an indication that other memory is corrupt.' 錯誤，程式直接當掉。

查到一篇文章提到開發環境正常但丟到生產環境出錯的案例：解决SQLite database is locked by 深蓝色左手，該文實測 SQLite 在高度並用環境下較易出錯(機器效能差反而不易出錯)，而我實測結果是實體 Windows OK，Hyper-V 與 Azure CentOS VM 都會出錯。進一步爬文，在 FAQ 找到一些先前忽略的線索：

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
SQLite 允許多 Process 共用讀取，但只有一個 Process 可以寫入。

SQLite uses reader/writer locks to control access to the database. But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem.
SQLite 使用檔案系統的讀寫鎖定控制資料庫存取，由此推測檔案系統鎖定的實作差異或許是在 Windows / Linux 出錯率不同的關鍵。

另外，依據 Stackoverflow 的一則討論：

SQLite, in its current versions anyway, does not support concurrent writes. There can be multiple reader processes simultaneously, but at most one writer. With Write-Ahead Logging enabled, that fact does not change. WAL enables more concurrency, but not write concurrency.
有個 Write-Ahead Logging 模式可以提高共用性，但無助於解決同時寫入問題，無法避免 database is locked 問題。

結論

如在程式中用多執行緒更新 SQLite 資料庫，需使用 lock 確保單一時間只有一條 Thread 更新資料庫以杜絕 database is locked 錯誤。

Got "database is locked" exception after ASP.NET Core deployed to CentOS server. After designing a experiment to reproduce the error and reading the references, I reach the conclusion that "lock" mechanism is required for multi-threading SQLite updating.


Comments

Be the first to post a comment

Post a comment