C#中SQLite的并发控制与多线程访问
并发控制的重要性
在多线程环境中访问SQLite数据库时，并发控制至关重要。不当的并发访问可能导致数据不一致、竞态条件和潜在的数据损坏。本文将详细探讨C#中SQLite的并发控制策略。
准备环境
安装 SQLite
首先，你需要在你的 C# 项目中安装 SQLite 的 NuGet 包：
复制
`Install-Package System.Data.SQLite`1.
图片
基本并发控制机制
锁机制示例
复制
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppLiteSql
{
publicclass DatabaseManager
{
// 创建一个静态锁对象，确保线程同步
privatestatic readonly object _lock = new object();
// 数据库连接字符串
privatestring _connectionString;
public DatabaseManager(string dbPath)
{
_connectionString = $"Data Source={dbPath};Versinotallow=3;";
}
// 线程安全的插入方法
public void ThreadSafeInsert(string name, int age)
{
// 使用锁确保同步
lock (_lock)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand(connection))
{
command.CommandText = @"
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age)";
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Age", age);
command.ExecuteNonQuery();
}
}
}
}
// 线程安全的查询方法
public int GetUserCount()
{
lock (_lock)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var command = new SQLiteCommand("SELECT COUNT(*) FROM Users", connection))
{
return Convert.ToInt32(command.ExecuteScalar());
}
}
}
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.
复制
namespace AppLiteSql
{
internal class Program
{
static void Main(string[] args)
{
DatabaseManager dbManager = new DatabaseManager("D:\\myproject\\11Test\\AppLiteSql\\db");
// 创建多个线程并发插入数据
var threads = new List<Thread>();
for (int i = 0; i < 10; i++)
{
int threadId = i;
var thread = new Thread(() =>
{
for (int j = 0; j < 100; j++)
{
dbManager.ThreadSafeInsert($"User_{threadId}_{j}", 30 + threadId);
}
});
threads.Add(thread);
thread.Start();
}
// 等待所有线程完成
foreach (var thread in threads)
{
thread.Join();
}
// 验证插入结果
int totalUsers = dbManager.GetUserCount();
Console.WriteLine($"Total Users: {totalUsers}");
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.
图片
高级并发控制策略
信号量控制数据库连接池
复制
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppLiteSql
{
publicclass AdvancedDatabaseManager
{
// 使用信号量控制并发连接数
private readonly SemaphoreSlim _connectionSemaphore;
privatestring _connectionString;
public AdvancedDatabaseManager(string dbPath, int maxConcurrentConnections = 5)
{
_connectionString = $"Data Source={dbPath};Versinotallow=3;";
_connectionSemaphore = new SemaphoreSlim(maxConcurrentConnections);
}
// 异步并发查询方法
public async Task<int> ConcurrentQueryAsync(string query)
{
await _connectionSemaphore.WaitAsync();
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = new SQLiteCommand(query, connection))
{
return Convert.ToInt32(await command.ExecuteScalarAsync());
}
}
}
finally
{
_connectionSemaphore.Release();
}
}
// 异步读取多行数据的方法
public async Task<List<User>> ReadUsersAsync(string condition = null)
{
await _connectionSemaphore.WaitAsync();
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
string query = "SELECT Id, Name, Age FROM Users";
if (!string.IsNullOrEmpty(condition))
{
query += $" WHERE {condition}";
}
using (var command = new SQLiteCommand(query, connection))
{
var users = new List<User>();
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
users.Add(new User
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Age = reader.GetInt32(2)
});
}
}
return users;
}
}
}
finally
{
_connectionSemaphore.Release();
}
}
// 异步写入数据的方法
public async Task<int> WriteUserAsync(User user)
{
await _connectionSemaphore.WaitAsync();
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = new SQLiteCommand(connection))
{
command.CommandText = @"
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age);
SELECT last_insert_rowid();";
command.Parameters.AddWithValue("@Name", user.Name);
command.Parameters.AddWithValue("@Age", user.Age);
return Convert.ToInt32(await command.ExecuteScalarAsync());
}
}
}
finally
{
_connectionSemaphore.Release();
}
}
// 异步批量写入数据的方法
public async Task BulkWriteUsersAsync(List<User> users)
{
await _connectionSemaphore.WaitAsync();
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
try
{
using (var command = new SQLiteCommand(connection))
{
command.CommandText = @"
INSERT INTO Users (Name, Age)
VALUES (@Name, @Age)";
var nameParam = command.Parameters.Add("@Name", System.Data.DbType.String);
var ageParam = command.Parameters.Add("@Age", System.Data.DbType.Int32);
foreach (var user in users)
{
nameParam.Value = user.Name;
ageParam.Value = user.Age;
await command.ExecuteNonQueryAsync();
}
}
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
}
}
}
finally
{
_connectionSemaphore.Release();
}
}
// 异步更新数据的方法
public async Task<int> UpdateUserAsync(int id, User updatedUser)
{
await _connectionSemaphore.WaitAsync();
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = new SQLiteCommand(connection))
{
command.CommandText = @"
UPDATE Users
SET Name = @Name, Age = @Age
WHERE Id = @Id";
command.Parameters.AddWithValue("@Name", updatedUser.Name);
command.Parameters.AddWithValue("@Age", updatedUser.Age);
command.Parameters.AddWithValue("@Id", id);
return await command.ExecuteNonQueryAsync();
}
}
}
finally
{
_connectionSemaphore.Release();
}
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.124.125.126.127.128.129.130.131.132.133.134.135.136.137.138.139.140.141.142.143.144.145.146.147.148.149.150.151.152.153.154.155.156.157.158.159.160.161.162.163.164.165.166.167.168.169.170.171.172.173.174.175.176.177.178.179.180.181.182.183.184.185.186.187.188.189.190.
复制
namespace AppLiteSql
{
internal class Program
{
static async Task Main(string[] args)
{
AdvancedDatabaseManager dbManager = new AdvancedDatabaseManager("D:\\myproject\\11Test\\AppLiteSql\\db");
// 写入单个用户
var newUser = new User { Name = "John Doe", Age = 30 };
int newUserId = await dbManager.WriteUserAsync(newUser);
// 批量写入用户
var userList = new List<User>
{
new User { Name = "Alice", Age = 25 },
new User { Name = "Bob", Age = 35 }
};
await dbManager.BulkWriteUsersAsync(userList);
// 读取用户
var users = await dbManager.ReadUsersAsync("Age > 20");
foreach (var user in users)
{
Console.WriteLine($"User: {user.Name}, Age: {user.Age}");
}
// 更新用户
var updatedUser = new User { Name = "John Smith", Age = 31 };
await dbManager.UpdateUserAsync(newUserId, updatedUser);
}
}
}1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.
图片
最佳实践与注意事项
始终使用参数化查询防止SQL注入尽量缩小锁的作用范围考虑使用异步方法处理数据库操作对于高并发场景，考虑使用连接池定期检查和优化数据库性能性能建议
对于读多写少的场景，考虑使用读写锁使用批量插入减少数据库连接开销优化查询语句和索引总结
SQLite的并发控制需要谨慎处理。通过合理的锁机制、信号量控制和异步编程，可以有效管理多线程环境下的数据库访问。关键在于平衡线程安全性和性能。
希望这篇文章能帮助您深入理解C#中SQLite的并发控制与多线程访问。建议根据具体业务场景选择最适合的并发策略。
阅读剩余
THE END
