C#中SQLite的并發(fā)控制與多線程訪問
作者：iamrick
SQLite的并發(fā)控制需要謹(jǐn)慎處理。通過合理的鎖機(jī)制、信號(hào)量控制和異步編程，可以有效管理多線程環(huán)境下的數(shù)據(jù)庫訪問。關(guān)鍵在于平衡線程安全性和性能。
并發(fā)控制的重要性
在多線程環(huán)境中訪問SQLite數(shù)據(jù)庫時(shí)，并發(fā)控制至關(guān)重要。不當(dāng)?shù)牟l(fā)訪問可能導(dǎo)致數(shù)據(jù)不一致、競態(tài)條件和潛在的數(shù)據(jù)損壞。本文將詳細(xì)探討C#中SQLite的并發(fā)控制策略。
準(zhǔn)備環(huán)境
安裝 SQLite
首先，你需要在你的 C# 項(xiàng)目中安裝 SQLite 的 NuGet 包：
`Install-Package System.Data.SQLite`
圖片
基本并發(fā)控制機(jī)制
鎖機(jī)制示例
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppLiteSql
{
publicclass DatabaseManager
{
// 創(chuàng)建一個(gè)靜態(tài)鎖對(duì)象，確保線程同步
privatestatic readonly object _lock = new object();
// 數(shù)據(jù)庫連接字符串
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");
// 創(chuàng)建多個(gè)線程并發(fā)插入數(shù)據(jù)
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();
}
// 驗(yàn)證插入結(jié)果
int totalUsers = dbManager.GetUserCount();
Console.WriteLine($"Total Users: {totalUsers}");
}
}
}
圖片
高級(jí)并發(fā)控制策略
信號(hào)量控制數(shù)據(jù)庫連接池
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppLiteSql
{
publicclass AdvancedDatabaseManager
{
// 使用信號(hào)量控制并發(fā)連接數(shù)
private readonly SemaphoreSlim _connectionSemaphore;
privatestring _connectionString;
public AdvancedDatabaseManager(string dbPath, int maxConcurrentConnections = 5)
{
_connectionString = $"Data Source={dbPath};Versinotallow=3;";
_connectionSemaphore = new SemaphoreSlim(maxConcurrentConnections);
}
// 異步并發(fā)查詢方法
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();
}
}
// 異步讀取多行數(shù)據(jù)的方法
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();
}
}
// 異步寫入數(shù)據(jù)的方法
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();
}
}
// 異步批量寫入數(shù)據(jù)的方法
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();
}
}
// 異步更新數(shù)據(jù)的方法
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");
// 寫入單個(gè)用戶
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);
}
}
}
圖片
最佳實(shí)踐與注意事項(xiàng)
- 始終使用參數(shù)化查詢防止SQL注入
- 盡量縮小鎖的作用范圍
- 考慮使用異步方法處理數(shù)據(jù)庫操作
- 對(duì)于高并發(fā)場景，考慮使用連接池
- 定期檢查和優(yōu)化數(shù)據(jù)庫性能
性能建議
- 對(duì)于讀多寫少的場景，考慮使用讀寫鎖
- 使用批量插入減少數(shù)據(jù)庫連接開銷
- 優(yōu)化查詢語句和索引
總結(jié)
SQLite的并發(fā)控制需要謹(jǐn)慎處理。通過合理的鎖機(jī)制、信號(hào)量控制和異步編程，可以有效管理多線程環(huán)境下的數(shù)據(jù)庫訪問。關(guān)鍵在于平衡線程安全性和性能。
希望這篇文章能幫助您深入理解C#中SQLite的并發(fā)控制與多線程訪問。建議根據(jù)具體業(yè)務(wù)場景選擇最適合的并發(fā)策略。
責(zé)任編輯：武曉燕
來源：
技術(shù)老小子
