在现代高并发应用中，多线程环境下的数据访问是必须面对的重要课题。SqlSugar作为高性能ORM框架，提供了完善的多线程支持。本文将深入探讨SqlSugar在多线程环境下的安全高效CRUD实践。
1. 多线程环境下的连接管理
1.1 线程安全的SqlSugarClient管理
public class ThreadSafeSqlSugarManager
{
private static readonly ConcurrentDictionary<string, Lazy<ISqlSugarClient>> _clients
= new ConcurrentDictionary<string, Lazy<ISqlSugarClient>>();
// 获取线程安全的数据库实例
public static ISqlSugarClient GetClient(string connectionName = "Default")
{
return _clients.GetOrAdd(connectionName, key =>
new Lazy<ISqlSugarClient>(() => CreateSqlSugarClient(key))
).Value;
}
private static ISqlSugarClient CreateSqlSugarClient(string connectionName)
{
var connectionString = GetConnectionString(connectionName);
return new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = connectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true, // 自动关闭连接
InitKeyType = InitKeyType.Attribute,
// 多线程重要配置
MoreSettings = new ConnMoreSettings()
{
// 启用ADO.NET连接池
EnablePool = true,
// 连接池大小
PoolMaxSize = 100,
PoolMinSize = 10
},
// 配置外部服务
ConfigureExternalServices = new ConfigureExternalServices()
{
// 启用SQL执行监控
SqlFuncServices = SqlFuncConfigs.All
}
});
}
}
1.2 依赖注入中的生命周期管理
// 在Startup.cs或Program.cs中配置
public void ConfigureServices(IServiceCollection services)
{
// 使用AddScoped确保每个请求使用独立的SqlSugar实例
services.AddScoped<ISqlSugarClient>(provider =>
{
var httpContext = provider.GetService<IHttpContextAccessor>()?.HttpContext;
var connectionString = GetConnectionString(httpContext);
return new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = connectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
// 多线程配置
MoreSettings = new ConnMoreSettings()
{
EnablePool = true,
PoolMaxSize = 50
}
});
});
// 注册仓储服务
services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
}
2. 多线程安全的数据访问层
2.1 线程安全的仓储实现
public class ThreadSafeRepository<T> : IRepository<T> where T : class, new()
{
private readonly ISqlSugarClient _db;
private readonly AsyncLocal<ISqlSugarClient> _threadLocalDb;
private readonly object _lockObject = new object();
public ThreadSafeRepository(ISqlSugarClient db)
{
_db = db;
_threadLocalDb = new AsyncLocal<ISqlSugarClient>();
}
// 获取线程专用的数据库实例
private ISqlSugarClient GetThreadSafeDb()
{
if (_threadLocalDb.Value == null)
{
lock (_lockObject)
{
if (_threadLocalDb.Value == null)
{
// 复制配置创建新实例，确保线程安全
_threadLocalDb.Value = new SqlSugarClient(_db.CurrentConnectionConfig.Clone());
}
}
}
return _threadLocalDb.Value;
}
// 线程安全的查询
public async Task<List<T>> GetListAsync(Expression<Func<T, bool>> whereExpression = null)
{
var db = GetThreadSafeDb();
var query = db.Queryable<T>();
if (whereExpression != null)
{
query = query.Where(whereExpression);
}
return await query.ToListAsync();
}
// 线程安全的插入
public async Task<bool> InsertAsync(T entity)
{
var db = GetThreadSafeDb();
return await db.Insertable(entity).ExecuteCommandAsync() > 0;
}
}
2.2 并发安全的工作单元
public class ConcurrentUnitOfWork : IUnitOfWork
{
private readonly ConcurrentDictionary<Type, object> _repositories;
private readonly ISqlSugarClient _db;
private readonly AsyncLocal<ISqlSugarClient> _scopedDb;
public ConcurrentUnitOfWork(ISqlSugarClient db)
{
_db = db;
_repositories = new ConcurrentDictionary<Type, object>();
_scopedDb = new AsyncLocal<ISqlSugarClient> { Value = db.CopyNew() };
}
public IRepository<T> GetRepository<T>() where T : class, new()
{
return (IRepository<T>)_repositories.GetOrAdd(typeof(T),
t => new Repository<T>(_scopedDb.Value));
}
public void BeginTransaction()
{
_scopedDb.Value.Ado.BeginTran();
}
public void CommitTransaction()
{
try
{
_scopedDb.Value.Ado.CommitTran();
}
finally
{
_scopedDb.Value.Dispose();
}
}
public void RollbackTransaction()
{
try
{
_scopedDb.Value.Ado.RollbackTran();
}
finally
{
_scopedDb.Value.Dispose();
}
}
}
3. 多线程批量操作
3.1 并行批量查询
public class ParallelQueryService
{
private readonly ISqlSugarClient _db;
private readonly SemaphoreSlim _semaphore;
public ParallelQueryService(ISqlSugarClient db, int maxConcurrency = 10)
{
_db = db;
_semaphore = new SemaphoreSlim(maxConcurrency);
}
// 并行分页查询
public async Task<List<T>> ParallelPagedQuery<T>(
Expression<Func<T, bool>> whereExpression,
int totalPages,
int pageSize) where T : class, new()
{
var results = new ConcurrentBag<List<T>>();
var tasks = new List<Task>();
// 创建并行查询任务
for (int pageIndex = 1; pageIndex <= totalPages; pageIndex++)
{
await _semaphore.WaitAsync();
var task = Task.Run(async () =>
{
try
{
using (var scopedDb = _db.CopyNew())
{
var pageData = await scopedDb.Queryable<T>()
.Where(whereExpression)
.ToPageListAsync(pageIndex, pageSize);
if (pageData.Any())
{
results.Add(pageData);
}
}
}
finally
{
_semaphore.Release();
}
});
tasks.Add(task);
}
await Task.WhenAll(tasks);
return results.SelectMany(x => x).ToList();
}
// 数据分片并行查询
public async Task<List<T>> ShardedParallelQuery<T>(
Expression<Func<T, bool>> baseCondition,
string shardKey,
int shardCount) where T : class, new()
{
var tasks = Enumerable.Range(0, shardCount).Select(async shardId =>
{
using (var scopedDb = _db.CopyNew())
{
return await scopedDb.Queryable<T>()
.Where(baseCondition)
.Where($"{shardKey} % {shardCount} = {shardId}")
.ToListAsync();
}
});
var results = await Task.WhenAll(tasks);
return results.SelectMany(x => x).ToList();
}
}
3.2 线程安全的批量写入
public class ThreadSafeBulkOperator
{
private readonly ISqlSugarClient _db;
private readonly ReaderWriterLockSlim _lock = new ReaderWriterLockSlim();
public ThreadSafeBulkOperator(ISqlSugarClient db)
{
_db = db;
}
// 线程安全的批量插入
public async Task<BulkOperationResult> ThreadSafeBulkInsert<T>(
List<T> entities,
BulkInsertConfig config = null) where T : class, new()
{
config ??= new BulkInsertConfig();
var result = new BulkOperationResult();
// 使用读写锁确保线程安全
_lock.EnterWriteLock();
try
{
using (var transaction = _db.Ado.BeginTran())
{
try
{
if (entities.Count <= config.BatchThreshold)
{
result.AffectedRows = await _db.Insertable(entities).ExecuteCommandAsync();
}
else
{
// 分批次插入
result.AffectedRows = await BatchInsertWithLock(entities, config.BatchSize);
}
transaction.Commit();
result.Success = true;
}
catch (Exception ex)
{
transaction.Rollback();
result.Error = ex.Message;
result.Success = false;
}
}
}
finally
{
_lock.ExitWriteLock();
}
return result;
}
// 带锁的批量操作
private async Task<int> BatchInsertWithLock<T>(List<T> entities, int batchSize) where T : class, new()
{
var total = 0;
for (int i = 0; i < entities.Count; i += batchSize)
{
var batch = entities.Skip(i).Take(batchSize).ToList();
total += await _db.Insertable(batch).ExecuteCommandAsync();
// 短暂释放锁，避免长时间阻塞
if (i + batchSize < entities.Count)
{
_lock.ExitWriteLock();
await Task.Delay(1);
_lock.EnterWriteLock();
}
}
return total;
}
}
4. 并发控制与锁机制
4.1 乐观锁控制
public class OptimisticLockService
{
// 使用版本号控制并发
public async Task<ConcurrentResult> UpdateWithOptimisticLock<T>(
long id,
Action<T> updateAction) where T : class, IVersionEntity, new()
{
int retryCount = 0;
const int maxRetry = 3;
while (retryCount < maxRetry)
{
using (var db = _db.CopyNew())
{
db.Ado.BeginTran();
try
{
// 查询当前数据和版本号
var entity = await db.Queryable<T>()
.Where(x => x.Id == id)
.FirstAsync();
if (entity == null)
{
return ConcurrentResult.NotFound();
}
var oldVersion = entity.Version;
// 执行更新操作
updateAction(entity);
entity.Version++; // 版本号增加
// 使用版本号作为更新条件
var affected = await db.Updateable(entity)
.Where(x => x.Id == id && x.Version == oldVersion)
.ExecuteCommandAsync();
if (affected > 0)
{
db.Ado.CommitTran();
return ConcurrentResult.Success();
}
else
{
db.Ado.RollbackTran();
retryCount++;
await Task.Delay(100 * retryCount); // 指数退避
}
}
catch (Exception ex)
{
db.Ado.RollbackTran();
return ConcurrentResult.Error(ex.Message);
}
}
}
return ConcurrentResult.Conflict("更新冲突，重试次数超限");
}
}
public interface IVersionEntity
{
int Version { get; set; }
}
4.2 分布式锁控制
public class DistributedLockService
{
private readonly IDistributedLock _distributedLock;
private readonly ISqlSugarClient _db;
public async Task<ConcurrentResult> UpdateWithDistributedLock(
string resourceKey,
long entityId,
Func<Task> updateOperation)
{
// 获取分布式锁
await using var lockHandle = await _distributedLock.TryAcquireLockAsync(
resourceKey, TimeSpan.FromSeconds(30));
if (lockHandle == null)
{
return ConcurrentResult.Conflict("获取锁失败");
}
try
{
using (var db = _db.CopyNew())
{
db.Ado.BeginTran();
try
{
await updateOperation();
db.Ado.CommitTran();
return ConcurrentResult.Success();
}
catch (Exception ex)
{
db.Ado.RollbackTran();
return ConcurrentResult.Error(ex.Message);
}
}
}
finally
{
await lockHandle.DisposeAsync();
}
}
}
SqlSugar多线程安全高效CRUD的关键要点：
线程安全基础：
-
连接管理：每个线程使用独立的SqlSugarClient实例
-
资源隔离：使用AsyncLocal、ThreadLocal确保线程间隔离
-
锁机制：合理使用读写锁、信号量控制并发
并发控制策略：
-
乐观锁：通过版本号控制数据一致性
-
分布式锁：跨进程并发控制
-
死锁处理：实现重试机制和指数退避
性能优化重点：
-
连接池：合理配置连接池参数
-
并行处理：使用Parallel、Task.WhenAll等并行技术
-
分批处理：大数据量分批并行处理
监控保障：
-
性能监控：实时监控多线程操作性能
-
错误处理：完善的异常处理和重试机制
-
资源清理：确保线程结束时资源正确释放
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
