SQLite的并发问题

   转载自：C# 下 SQLite 并发操作与锁库问题的 5 种解决方案_51CTO博客_sqlcipher c#

SQLite是轻量级的数据库，可用于嵌入式设备，仅需几百KB的内存即可工作，整个数据库存储在单一文件中，便于管理，迁移，备份。无需繁琐配置。

轻量高性能必然带来一定的局限，这次遇到的就是SQLite数据库的并发操作问题

关键点：在多线程或多进程并发访问的场景下，同一时刻仅允许单个线程进行写入操作。

现象：database is locked 错误

问题描述：在一个线程正在执行写入操作的过程中，另一个线程的写入请求只能被迫等待，等待时间过长超过系统预设的超时时间（通常5s可更改）就会触发此错误。

解决方案

1. 读写锁

2. 事务

3. WAL模式

4. 连接池

5. 多线程模式

详细说明

1. 读写锁

确保资源不被争抢导致崩溃

读写锁

using System.Threading;

public class SqliteDataManager
{
    //ReaderWriterLockSlim类提供读写锁
    private static ReaderWriterLockSlim _rwLock = new ReaderWriterLockSlim;
    private static string _connString = "Data Source=mydb.db;Version=3;";

    // 读操作（允许多线程并发）
    public void ReadData
    {
        _rwLock.EnterReadLock;  // 获取读锁
        try
        {
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open;
                var cmd = conn.CreateCommand;
                cmd.CommandText = "SELECT * FROM MyTable";
                using (var reader = cmd.ExecuteReader)
                {
                    while (reader.Read) { /* 读取数据 */ }
                }
            }
        }
        finally
        {
            _rwLock.ExitReadLock;  // 必须在finally中释放
        }
    }

    // 写操作（独占锁）
    public void WriteData(string value)
    {
        _rwLock.EnterWriteLock;  // 获取写锁
        try
        {
            using (var conn = new SQLiteConnection(_connString))
            {
                conn.Open;
                var cmd = conn.CreateCommand;
                cmd.CommandText = "INSERT INTO MyTable VALUES (@Value)";
                cmd.Parameters.AddWithValue("@Value", value);
                cmd.ExecuteNonQuery;
            }
        }
        finally
        {
            _rwLock.ExitWriteLock;  // 必须释放
        }
    }
}

可升级为写锁的读锁，可减少锁竞争，避免重复加锁

可升级为写锁的读锁

public sealed class SqliteConnectionManager
{
    private static readonly ReaderWriterLockSlim _lock = new ReaderWriterLockSlim;
    private static SQLiteConnection _conn;

    public static SQLiteConnection GetConnection
    {
        _lock.EnterUpgradeableReadLock;  // 可升级为写锁的读锁
        try
        {
            if (_conn == null)
            {
                _lock.EnterWriteLock;  // 升级为写锁
                try
                {
                    if (_conn == null)
                    {
                        _conn = new SQLiteConnection("Data Source=mydb.db");
                        _conn.Open;
                    }
                }
                finally { _lock.ExitWriteLock; }
            }
            return _conn;
        }
        finally { _lock.ExitUpgradeableReadLock; }
    }
}

具体使用

查看代码

using System;
using System.Data.SQLite;
using System.Threading;

class DatabaseManager
{
    private static SQLiteConnection _connection;
    private static readonly ReaderWriterLockSlim _lock = new ReaderWriterLockSlim();
    
    public static SQLiteConnection GetConnection()
    {
        if(_connection==null)
        {
            _lock.EnterWriteLock;
            try
            {
                if(_connection == null)
                {
                    _connection=new SQLiteConnection("Data Source=database.db");
                }
            }
            finally
            {
                _lock.ExitWriteLock();
            }
            return _connection;
        }
        
        public static void InsertUser(string name) 
        { 
            var connection = GetConnection();
            _lock.EnterWriteLock(); 
            try 
            { 
                //写锁中使用了事务
                using (var transaction = connection.BeginTransaction()) 
                { 
                    try 
                    { 
                        using (var command = new SQLiteCommand(connection)) 
                        { 
                            command.CommandText = "INSERT INTO Users (Name) VALUES (@name)";                        
                            command.Parameters.AddWithValue("@name", name); command.ExecuteNonQuery(); 
                        } 
                        transaction.Commit(); 
                    } 
                    catch (Exception ex) 
                    { 
                        transaction.Rollback(); 
                    } 
                } 
            } 
            finally 
            { 
                _lock.ExitWriteLock(); 
            }
        }
        
        public static void SelectUsers() 
        { 
            var connection = GetConnection(); 
            _lock.EnterReadLock(); 
            try 
            { 
                using (var command = new SQLiteCommand("SELECT * FROM Users", connection)) 
                { 
                    using (var reader = command.ExecuteReader()) 
                    { 
                        while (reader.Read()) 
                        { 
                            Console.WriteLine(reader["Name"]); 
                        } 
                    } 
                } 
            } 
            finally 
            { 
                _lock.ExitReadLock(); 
            }
        }
    }
 }
}

2.事务

保障数据完整性，事务具有原子性，内部的所有操作要么成功要么一起失败并回滚到插入之前的状态。一般只在读的时候使用事务。

在写入时使用事务

public static void InsertUser(string name) 
{ 
    var connection = GetConnection(); 
    using (var transaction = connection.BeginTransaction()) 
    { 
        try 
        { 
            using (var command = new SQLiteCommand(connection)) 
            { 
                command.CommandText = "INSERT INTO Users (Name) VALUES (@name)"; 
                command.Parameters.AddWithValue("@name", name); 
                command.ExecuteNonQuery(); 
            } 
            transaction.Commit(); 
        } 
        catch (Exception ex) 
        { 
            transaction.Rollback(); 
        }
    } 
}

3. WAL模式

Write-Ahead Logging

_connection = new SQLiteConnection("Data Source=database.db;Journal Mode=WAL");

在连接字符串中，加入Journal Mode =WAL即可开启WAL模式，此模式可读写并行

原理：将写入的数据暂存到WAL文件中，在这个过程中主数据库文件依然可以对外提供读服务，当文件满足一定条件（如WAL文件大小达到阈值、事务提交等）时，数据库引擎在后台将WAL文件中的数据合并到主数据库文件中。

4. 连接池

_connection = new SQLiteConnection("Data Source=database.db;Max Pool Size=100;Pooling=True");

预先创建一定数量的数据库连接，减少连接创建与销毁开销，但需要合理配置连接池参数，否则可能出现连接泄露或资源浪费

5 多线程模式（即综合使用）