C#でSQLiteをマルチスレッドで利用する

はじめに

C#でSQLiteをマルチスレッドで利用することになり、情報を集めたのですが、結論が明確に出せなかったので動作確認をした際の記録となります。

調査内容

• SQLiteは基本はマルチスレッドに対応している。
• C#でマルチスレッドで使う場合、スレッド毎のコネクションにすべきと言う話と、コネクションを共用すべきという話が双方出てくる。
• またコネクションをプール可能という情報もある。

テスト内容

• 100個のスレッドで一個のテーブルに対して挿入・更新・削除を実施してみる。
• 以下のコードを元に条件を修正しながら動作検証をした。

TEST1:テストコードの基礎

TestSQLite.cs

using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp4
{
    internal class TestSQLite
    {
        public void CreateTable()
        {
            using (var myconnection = new SQLiteConnection(sqlConnectionSb.ToString()))
            {
                myconnection.Open();

                using (var cmd = new SQLiteCommand(myconnection))
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS test1(" +
                        "ID TEXT NOT NULL PRIMARY KEY," +
                        "Name TEXT NOT NULL)";

                    cmd.ExecuteNonQuery();
                }
            }
        }

        private string sqlConnectionSb = @"Data Source=C:\tmp\test.db; Version=3;Pooling=True;Max Pool Size=100;";
        //private string sqlConnectionSb = @"Data Source=C:\tmp\test.db; Version=3;Pooling=False;";

        public void WriteToDB()
        {
            try
            {
                using (SQLiteConnection myconnection = new SQLiteConnection(sqlConnectionSb.ToString()))
                {
                    myconnection.Open();
                    using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
                    {
                        using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
                        {
                            Guid id = Guid.NewGuid();

                            mycommand.CommandText = "INSERT INTO test1(ID, Name) VALUES ('" + id.ToString() + "', 'TEST1')";
                            mycommand.ExecuteNonQuery();

                            mycommand.CommandText = "UPDATE test1 SET Name='TEST2' WHERE ID='" + id.ToString() + "'";
                            mycommand.ExecuteNonQuery();

                            mycommand.CommandText = "DELETE FROM test1 WHERE ID='" + id.ToString() + "'";
                            mycommand.ExecuteNonQuery();
                        }
                        mytransaction.Commit();
                        //Console.WriteLine("Done");
                    }
                }
            }
            catch (SQLiteException ex)
            {
                if (ex.ResultCode == SQLiteErrorCode.Busy)
                {
                    Console.WriteLine("SQLiteErrorCode.Busy");
                }
                else
                {
                    Console.WriteLine(ex);
                }
            }
        }

        internal async Task<int> TEST3()
        {
            await Task.Run(() => { 
                CreateTable();

                var tasks = new Task[100];

                for (int i = 0; i < 100; i++)
                {
                    tasks[i] = new Task(()=> {
                        for (int j = 0; j < 30; ++j)
                        {
                            WriteToDB();
                        }
                    });

                    tasks[i].Start();
                }

                foreach (var task in tasks)
                {
                    task.Wait();
                }
            });
            return 0;
        }
    }
}

TEST1:テスト結果

• 例外やBUSYは発生しなかった。
• プールの効果は不明で、Falseにしても同様の結果になる。
• CPU使用率があまり上がらないのでシーケンシャルに動作しているようである。
• 別のプロセスから開くとSQLiteErrorCode.Busyが発生する。

TEST2:BeginTransactionを利用しないように変更

• 次のエラーが頻発する。

SQLite error (5): database is locked in ...

• トランザクションを使用しないとスレッド毎のSQLiteConnectionでもロックエラーが発生している。

TEST3:ExecuteNonQueryAcyncを使用するように変更

• 同様に次のエラーが発生する。
• これは呼び出し方法の変更なので期待していなかったが予想通りエラーが出る。

SQLite error (5): database is locked in ...

TEST4:SQLiteConnectionを共用しスレッド毎にBeginTransaction()

• 次のエラーが出て利用不可になる。
• どうもトランザクション中にもう一回トランザクションを呼び出す動きになるので動かない。

SQLite error (1): statement aborts at 3: [BEGIN IMMEDIATE;] cannot start a transaction within a transaction
code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): SQL logic error
cannot start a transaction within a transaction

TEST5:SQLiteConnectionを共用し、BeginTransactionを利用しない

• 問題なく動作しロックのエラーは出ない。
• 処理時間は最も早く終了する。

整理

• SQLiteConnectionのインスタンス毎にSQLite自体が同期化をしているようである。
• SQLiteConnectionのインスタンスはそれぞれが一個しかTransactionにはいれないようである。
• スレッド毎のSQLiteConnectionに対してBeginTransaction～EndTransactionを使用すると、他のスレッドのSQLiteConnectionのBeginTransaction～EndTransactionの間は同期化されている。
• Transactionを使用する場合はSQLiteConnectionはスレッド毎のインスタンス。
• BeginTransactionを使用しない場合はSQLiteConnectionを共用できる。

My結論I

• Transactionを使用せず、SQLiteConnectionのインスタンスをスレッド間で共有する。
• SQLiteConnectionをスレッド毎のインスタンスにすると以下のエラーが出る。

SQLite error (5): database is locked in ...

My結論II

• SQLiteConnectionをスレッド毎のインスタンスとして、必ずBeginTransactionを使用する。
• Transactionを使用しないと以下のエラーが出る。

SQLite error (5): database is locked in ...

• SQLiteConnectionを共用してしまうとBeginTransaction自体がエラーになる。

参考情報

https://stackoverflow.com/questions/15383615/multiple-access-to-a-single-sqlite-database-file-via-system-data-sqlite-and-c-sh

https://stackoverflow.com/questions/9910013/one-sqliteconnection-per-thread