SQLite并发操作与锁库问题的处理方案

SQLite是一个轻量级的关系型数据库，在并发访问时可能会遇到数据库锁定的问题。本文将详细介绍如何在C#中正确处理SQLite的并发操作，避免锁库问题。

常见的锁库场景

SQLite在以下情况下容易发生锁库：

多线程同时写入数据
长时间事务占用连接
读写操作混合执行

基本解决方案

使用using语句确保连接释放

C#
public void SafeInsert(string data)
{
    using (var connection = new SQLiteConnection(_connectionString))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO TestTable (Data) VALUES (@data)";
            command.Parameters.AddWithValue("@data", data);
            command.ExecuteNonQuery();
        }
    }
}

设置超时时间

C#
public class DatabaseManager
{
    private readonly string _connectionString;
    
    public DatabaseManager(string dbPath)
    {
        var builder = new SQLiteConnectionStringBuilder
        {
            DataSource = dbPath,
            DefaultTimeout = 30, // 设置默认超时时间为30秒
            BusyTimeout = 5000  // 设置忙等待超时时间为5秒
        };
        _connectionString = builder.ConnectionString;
    }
}

完整例子

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

namespace AppSqlite
{
    public class DatabseManager : IDisposable
    {
        private readonly string _connectionString;
        private readonly string _dbPath;

        public DatabaseManager(string dbPath)
        {
            _dbPath = dbPath;
            // 构建连接字符串，添加一些常用配置  
            var builder = new SQLiteConnectionStringBuilder
            {
                DataSource = dbPath,
                Version = 3,
                DefaultTimeout = 30,
                BusyTimeout = 5000,
                JournalMode = SQLiteJournalModeEnum.Wal, // 使用WAL模式提高并发性能  
                Pooling = true,
                CacheSize = 2000,                       // 缓存大小  
                SyncMode = SynchronizationModes.Normal  // 同步模式  
            };
            _connectionString = builder.ConnectionString;

            InitializeDatabase();
        }

        // 初始化数据库  
        private void InitializeDatabase()
        {
            // 如果数据库文件不存在，则创建  
            if (!File.Exists(_dbPath))
            {
                SQLiteConnection.CreateFile(_dbPath);
            }

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    // 创建测试表  
                    command.CommandText = @"  
                    CREATE TABLE IF NOT EXISTS TestTable (  
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,  
                        Data TEXT NOT NULL,  
                        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP  
                    )";
                    command.ExecuteNonQuery();

                    // 创建索引  
                    command.CommandText = "CREATE INDEX IF NOT EXISTS IX_TestTable_Data ON TestTable(Data)";
                    command.ExecuteNonQuery();
                }
            }
        }

        // 安全的插入操作  
        public void SafeInsert(string data)
        {
            if (string.IsNullOrEmpty(data))
            {
                throw new ArgumentNullException(nameof(data));
            }

            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            using (var command = connection.CreateCommand())
                            {
                                command.Transaction = transaction;
                                command.CommandText = "INSERT INTO TestTable (Data) VALUES (@data)";
                                command.Parameters.AddWithValue("@data", data);
                                command.ExecuteNonQuery();
                            }
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
            catch (SQLiteException ex)
            {
                // 处理特定的SQLite异常  
                switch (ex.ResultCode)
                {
                    case SQLiteErrorCode.Busy:
                        throw new Exception("数据库正忙，请稍后重试", ex);
                    case SQLiteErrorCode.Locked:
                        throw new Exception("数据库被锁定，请稍后重试", ex);
                    case SQLiteErrorCode.IoErr:
                        throw new Exception("数据库IO错误", ex);
                    default:
                        throw new Exception("数据库操作失败", ex);
                }
            }
        }

        // 批量插入操作  
        public void BatchInsert(IEnumerable<string> dataList)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var transaction = connection.BeginTransaction())
                    {
                        try
                        {
                            using (var command = connection.CreateCommand())
                            {
                                command.Transaction = transaction;
                                command.CommandText = "INSERT INTO TestTable (Data) VALUES (@data)";
                                var parameter = command.Parameters.Add("@data", System.Data.DbType.String);

                                foreach (var data in dataList)
                                {
                                    parameter.Value = data;
                                    command.ExecuteNonQuery();
                                }
                            }
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception("批量插入失败", ex);
            }
        }

        // 查询数据  
        public List<(long Id, string Data, DateTime CreateTime)> QueryAll()
        {
            var results = new List<(long Id, string Data, DateTime CreateTime)>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT Id, Data, CreateTime FROM TestTable";
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add((
                                reader.GetInt64(0),
                                reader.GetString(1),
                                reader.GetDateTime(2)
                            ));
                        }
                    }
                }
            }

            return results;
        }

        public void Dispose()
        {
            // 在这里可以添加清理代码  
            GC.SuppressFinalize(this);
        }
    }

}

调用

C#
public static async Task Main()
{
    var dbPath = "test.db";

    if (File.Exists(dbPath))
    {
        File.Delete(dbPath);
    }

    using (var dbManager = new DatabaseManager(dbPath))
    {
        // 测试单条插入  
        Console.WriteLine("测试单条插入...");
        dbManager.SafeInsert("Test Data 1");

        // 测试批量插入  
        Console.WriteLine("测试批量插入...");
        var batchData = Enumerable.Range(1, 5)
            .Select(i => $"Batch Data {i}")
            .ToList();
        dbManager.BatchInsert(batchData);

        // 测试并发插入  
        Console.WriteLine("测试并发插入...");
        var tasks = Enumerable.Range(1, 10)
            .Select(i => Task.Run(() =>
                dbManager.SafeInsert($"Concurrent Data {i}")))
            .ToArray();
        await Task.WhenAll(tasks);

        // 查询并显示所有数据  
        Console.WriteLine("\n查询所有数据:");
        var results = dbManager.QueryAll();
        foreach (var (id, data, createTime) in results)
        {
            Console.WriteLine($"Id: {id}, Data: {data}, CreateTime: {createTime}");
        }
    }

    Console.WriteLine("\n所有测试完成！");
}

高级解决方案

实现重试机制

C#
using System;
using System.Data.SQLite;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.IO;

public class DatabaseRetryManager : IDisposable
{
    private readonly string _connectionString;
    private const int MaxRetries = 3;
    private const int RetryDelayMs = 1000;

    public DatabaseRetryManager(string dbPath)
    {
        var builder = new SQLiteConnectionStringBuilder
        {
            DataSource = dbPath,
            Version = 3,
            DefaultTimeout = 30,
            BusyTimeout = 5000,
            JournalMode = SQLiteJournalModeEnum.Wal,
            Pooling = true
        };
        _connectionString = builder.ConnectionString;

        InitializeDatabase(dbPath);
    }

    private void InitializeDatabase(string dbPath)
    {
        if (!File.Exists(dbPath))
        {
            SQLiteConnection.CreateFile(dbPath);
        }

        using var connection = new SQLiteConnection(_connectionString);
        connection.Open();
        using var command = connection.CreateCommand();

        // 创建示例表  
        command.CommandText = @"  
            CREATE TABLE IF NOT EXISTS Users (  
                Id INTEGER PRIMARY KEY AUTOINCREMENT,  
                Name TEXT NOT NULL,  
                Email TEXT UNIQUE,  
                CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP  
            );  
            
            CREATE TABLE IF NOT EXISTS Logs (  
                Id INTEGER PRIMARY KEY AUTOINCREMENT,  
                Message TEXT NOT NULL,  
                LogLevel TEXT NOT NULL,  
                Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP  
            );";
        command.ExecuteNonQuery();
    }

    // 基础重试方法  
    public async Task ExecuteWithRetryAsync(Func<SQLiteConnection, Task> action)
    {
        for (int i = 0; i < MaxRetries; i++)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    await action(connection);
                    return;
                }
            }
            catch (SQLiteException ex) when (IsTransientError(ex))
            {
                if (i == MaxRetries - 1)
                    throw;

                await LogRetryAttemptAsync(ex, i + 1);
                await Task.Delay(RetryDelayMs * (i + 1));
            }
        }
    }

    // 带返回值的重试方法  
    public async Task<T> ExecuteWithRetryAsync<T>(Func<SQLiteConnection, Task<T>> action)
    {
        for (int i = 0; i < MaxRetries; i++)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    return await action(connection);
                }
            }
            catch (SQLiteException ex) when (IsTransientError(ex))
            {
                if (i == MaxRetries - 1)
                    throw;

                await LogRetryAttemptAsync(ex, i + 1);
                await Task.Delay(RetryDelayMs * (i + 1));
            }
        }

        throw new Exception("重试次数已达上限");
    }

    private bool IsTransientError(SQLiteException ex)
    {
        return ex.ResultCode == SQLiteErrorCode.Busy ||
               ex.ResultCode == SQLiteErrorCode.Locked;
    }

    private async Task LogRetryAttemptAsync(SQLiteException ex, int attempt)
    {
        var logMessage = $"重试尝试 {attempt}/{MaxRetries}. 错误: {ex.Message}";
        await ExecuteWithoutRetryAsync(async conn =>
        {
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "INSERT INTO Logs (Message, LogLevel) VALUES (@message, @level)";
            cmd.Parameters.AddWithValue("@message", logMessage);
            cmd.Parameters.AddWithValue("@level", "Warning");
            await cmd.ExecuteNonQueryAsync();
        });
    }

    // 不带重试的执行方法（用于内部日志等操作）  
    private async Task ExecuteWithoutRetryAsync(Func<SQLiteConnection, Task> action)
    {
        using var connection = new SQLiteConnection(_connectionString);
        await connection.OpenAsync();
        await action(connection);
    }

    // 实用方法：添加用户  
    public async Task AddUserAsync(string name, string email)
    {
        await ExecuteWithRetryAsync(async connection =>
        {
            using var command = connection.CreateCommand();
            command.CommandText = "INSERT INTO Users (Name, Email) VALUES (@name, @email)";
            command.Parameters.AddWithValue("@name", name);
            command.Parameters.AddWithValue("@email", email);
            await command.ExecuteNonQueryAsync();
        });
    }

    // 实用方法：获取用户  
    public async Task<List<UserModel>> GetUsersAsync()
    {
        return await ExecuteWithRetryAsync(async connection =>
        {
            var users = new List<UserModel>();
            using var command = connection.CreateCommand();
            command.CommandText = "SELECT Id, Name, Email, CreatedAt FROM Users";

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                users.Add(new UserModel
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    Email = reader.GetString(2),
                    CreatedAt = reader.GetDateTime(3)
                });
            }

            return users;
        });
    }

    public void Dispose()
    {
        // 清理资源  
        GC.SuppressFinalize(this);
    }
}

public class UserModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

// 示例用法  
public class Program
{
    public static async Task Main()
    {
        var dbPath = "test.db";
        if (File.Exists(dbPath)) File.Delete(dbPath);

        using var dbManager = new DatabaseRetryManager(dbPath);

        // 测试添加用户  
        try
        {
            Console.WriteLine("添加用户...");
            await dbManager.AddUserAsync("John Doe", "john@example.com");
            await dbManager.AddUserAsync("Jane Doe", "jane@example.com");

            // 测试并发操作  
            var tasks = new List<Task>();
            for (int i = 0; i < 10; i++)
            {
                int index = i;
                tasks.Add(dbManager.AddUserAsync(
                    $"User{index}",
                    $"user{index}@example.com"
                ));
            }
            await Task.WhenAll(tasks);

            // 获取所有用户  
            Console.WriteLine("\n获取所有用户:");
            var users = await dbManager.GetUsersAsync();
            foreach (var user in users)
            {
                Console.WriteLine($"Id: {user.Id}, Name: {user.Name}, " +
                                $"Email: {user.Email}, Created: {user.CreatedAt}");
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"错误: {ex.Message}");
        }
    }
}

最佳实践总结

始终使用参数化查询，避免SQL注入
正确管理数据库连接的生命周期
实现适当的重试机制
设置合理的超时时间
在出现锁库时提供用户友好的错误提示

注意事项

SQLite不适合高并发的生产环境
避免长时间持有数据库连接
合理设计数据库结构，避免锁表
定期维护和优化数据库
监控数据库性能和锁定情况

通过以上方案，我们可以有效地处理SQLite在并发操作时的锁库问题，提高应用程序的稳定性和可靠性。

目录

常见的锁库场景

基本解决方案

使用using语句确保连接释放

设置超时时间

完整例子

高级解决方案

实现重试机制

最佳实践总结

注意事项