C#中SQLite的并發控制與多線程訪問
作者：iamrick
SQLite的并發控制需要謹慎處理。通過合理的鎖機制、信號量控制和異步編程，可以有效管理多線程環境下的數據庫訪問。關鍵在于平衡線程安全性和性能。
并發控制的重要性
在多線程環境中訪問SQLite數據庫時，并發控制至關重要。不當的并發訪問可能導致數據不一致、競態條件和潛在的數據損壞。本文將詳細探討C#中SQLite的并發控制策略。
準備環境
安裝 SQLite
首先，你需要在你的 C# 項目中安裝 SQLite 的 NuGet 包：
`Install-Package System.Data.SQLite`
圖片
基本并發控制機制
鎖機制示例
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());
}
}
}
}
}
}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}");
}
}
}
圖片
高級并發控制策略
信號量控制數據庫連接池
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();
}
}
}
}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);
}
}
}
圖片
最佳實踐與注意事項
- 始終使用參數化查詢防止SQL注入
- 盡量縮小鎖的作用范圍
- 考慮使用異步方法處理數據庫操作
- 對于高并發場景，考慮使用連接池
- 定期檢查和優化數據庫性能
性能建議
- 對于讀多寫少的場景，考慮使用讀寫鎖
- 使用批量插入減少數據庫連接開銷
- 優化查詢語句和索引
總結
SQLite的并發控制需要謹慎處理。通過合理的鎖機制、信號量控制和異步編程，可以有效管理多線程環境下的數據庫訪問。關鍵在于平衡線程安全性和性能。
希望這篇文章能幫助您深入理解C#中SQLite的并發控制與多線程訪問。建議根據具體業務場景選擇最適合的并發策略。
責任編輯：武曉燕
來源：
技術老小子
