背景
我正在尝试编写一个与SQLite DB一起使用的异步服务器。我正在将.NET Core与Entity Framework Core一起使用。
我正在将UnitOfWork与GenericRepository模式一起使用，但是如下面的示例代码所示，这与这些模式并没有真正的关系。
我正在使用Windows 10，但是我希望任何受支持的.NET Core平台都具有相同的行为。
我想实现的目标
我要实现的是合理的交易行为。我将整个问题简化为一个简单的方案，在该方案中，我调查数据库中是否存在某个对象，如果不存在，则添加该对象，否则将失败。整个操作都在一个事务中，并且预期的场景是:
一
二
三
显然，方案一和方案二可以完美地工作，因为只有一个线程在运行。问题出在数字三上。
问题
问题是，在情况三中我们进入步骤5时，整个事情都会崩溃。两个线程都有30秒的挂起时间，大多数情况下，两个线程都无法将对象添加到数据库中。
我知道如何使用全局应用程序锁定轻松解决此问题，但我想知道是否可以在不锁定的情况下解决此问题，从而保留用于数据库访问的异步/等待功能。
有时，一个线程设法添加对象，另一个线程失败，但是即使那样，两个线程也要花费那30秒才能完成操作，这完全不可用。
示例输出
17:41:18|first: Started
17:41:19|main: Press ENTER
17:41:19|second: Started
17:41:20|second: Object does not exist, entering wait ...
17:41:20|first: Object does not exist, entering wait ...
17:41:22|first: Wait done
17:41:22|second: Wait done
17:41:22|first: Call Insert
17:41:22|second: Call Insert
17:41:22|second: Call SaveThrowAsync
17:41:22|first: Call SaveThrowAsync
17:41:22|first: Call Commit
17:41:52|second: Exception: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Microsoft.Data.Sqlite.SqliteException: SQLite Error 5: 'database is locked'.
at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.<ExecuteDbDataReaderAsync>d__53.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.<ExecuteAsync>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__47.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<SaveChangesAsync>d__45.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.EntityFrameworkCore.DbContext.<SaveChangesAsync>d__30.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at ConsoleApp1.UnitOfWork.<SaveThrowAsync>d__6.MoveNext() in X:\Dev\NetCore\shit\test1\src\ConsoleApp1\UnitOfWork.cs:line 35
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.GetResult()
at ConsoleApp1.Program.<ThreadProc>d__2.MoveNext() in X:\Dev\NetCore\shit\test1\src\ConsoleApp1\Program.cs:line 72
17:41:52|first: Exception: Microsoft.Data.Sqlite.SqliteException: SQLite Error 5: 'database is locked'.
at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit()
at ConsoleApp1.Program.<ThreadProc>d__2.MoveNext() in X:\Dev\NetCore\shit\test1\src\ConsoleApp1\Program.cs:line 75
17:41:52|second: Finished
17:41:52|first: Finished
17:42:00|main: We have 0 object(s) in the database.
代码
我试图切断所有与保持最小无关的内容。如果要运行该程序，只需在Visual Studio中创建这些文件，等待.NET Core项目同步，编译该项目，运行“add-migration first”和“update-database”以创建数据库即可运行它。如果没有Visual Studio，则需要使用“dotnet”和“dotnet ef”命令。
Program.cs:
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
namespace ConsoleApp1
{
public class Program
{
public static void Main(string[] args)
{
Thread thread1 = new Thread(new ParameterizedThreadStart(ThreadProc));
thread1.Start("first");
Thread.Sleep(1000);
Thread thread2 = new Thread(new ParameterizedThreadStart(ThreadProc));
thread2.Start("second");
log("main", "Press ENTER");
Console.ReadLine();
using (UnitOfWork uow = new UnitOfWork())
{
IEnumerable<DatabaseObject> dbos = uow.DatabaseObjectRepository.GetAsync().Result;
log("main", "We have {0} object(s) in the database.", dbos.Count());
foreach (DatabaseObject dbo in dbos)
log("main", " -> id:{0}, value:{1}", dbo.DatabaseObjectId, dbo.Value);
}
}
public static void log(string Id, string Format, params object[] Args)
{
string prefix = string.Format("{0}|{1}: ", DateTime.Now.ToString("HH:mm:ss"), Id);
string msg = string.Format(prefix + Format, Args);
Console.WriteLine(msg);
}
public async static void ThreadProc(object State)
{
string id = (string)State;
log(id, "Started", id);
int ourObjectId = 1234;
using (UnitOfWork uow = new UnitOfWork())
{
using (IDbContextTransaction transaction = await uow.BeginTransactionAsync())
{
bool rollback = false;
try
{
DatabaseObject dbo = (await uow.DatabaseObjectRepository.GetAsync(o => o.DatabaseObjectId == ourObjectId)).FirstOrDefault();
if (dbo == null)
{
log(id, "Object does not exist, entering wait ...");
await Task.Delay(2000); // Same result with Thread.Sleep(2000) instead.
log(id, "Wait done");
dbo = new DatabaseObject()
{
DatabaseObjectId = ourObjectId,
Value = id
};
log(id, "Call Insert");
uow.DatabaseObjectRepository.Insert(dbo);
log(id, "Call SaveThrowAsync");
await uow.SaveThrowAsync();
log(id, "Call Commit");
transaction.Commit(); // .NET Core should commit automatically on transaction Dispose, but that does not work for me.
}
else
{
log(id, "Object already exists");
rollback = true;
}
}
catch (Exception exception)
{
log(id, "Exception: {0}", exception.ToString());
}
if (rollback)
{
log(id, "Rolling back");
transaction.Rollback();
}
}
}
log(id, "Finished");
}
}
}
UnitOfWork.cs:
using Microsoft.EntityFrameworkCore.Storage;
using System;
using System.Threading.Tasks;
namespace ConsoleApp1
{
public class UnitOfWork : IDisposable
{
private DatabaseContext context = null;
public DatabaseContext Context
{
get
{
if (context == null)
context = new DatabaseContext();
return context;
}
}
private GenericRepository<DatabaseObject> databaseObjectRepository;
public GenericRepository<DatabaseObject> DatabaseObjectRepository
{
get
{
if (databaseObjectRepository == null)
databaseObjectRepository = new GenericRepository<DatabaseObject>(Context);
return databaseObjectRepository;
}
}
public async Task SaveThrowAsync()
{
await Context.SaveChangesAsync();
}
public async Task<IDbContextTransaction> BeginTransactionAsync()
{
return await Context.Database.BeginTransactionAsync();
}
private bool disposed = false;
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool Disposing)
{
if (disposed) return;
if (Disposing)
{
if (context != null) context.Dispose();
context = null;
disposed = true;
}
}
}
}
GenericRepository.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp1
{
public class GenericRepository<TEntity> where TEntity : class
{
internal DatabaseContext context;
internal DbSet<TEntity> dbSet;
public GenericRepository(DatabaseContext context)
{
this.context = context;
dbSet = context.Set<TEntity>();
}
public virtual async Task<IEnumerable<TEntity>> GetAsync(Expression<Func<TEntity, bool>> filter = null)
{
IQueryable<TEntity> query = dbSet;
if (filter != null)
query = query.Where(filter);
List<TEntity> result = await query.ToListAsync();
return result;
}
public virtual void Insert(TEntity entity)
{
dbSet.Add(entity);
}
public virtual void Update(TEntity entityToUpdate)
{
dbSet.Attach(entityToUpdate);
context.Entry(entityToUpdate).State = EntityState.Modified;
}
}
}
DatabaseObject.cs:
namespace ConsoleApp1
{
public class DatabaseObject
{
public int DatabaseObjectId { get; set; }
public string Value { get; set; }
}
}
DatabaseContext.cs:
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp1
{
public class DatabaseContext : DbContext
{
public DbSet<DatabaseObject> DatabaseObjects { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Filename=mysqlite.db");
}
}
}
project.json:
{
"version": "1.0.0-*",
"buildOptions": {
"emitEntryPoint": true
},
"dependencies": {
"Microsoft.EntityFrameworkCore.Sqlite": "1.0.0",
"Microsoft.EntityFrameworkCore.Design": {
"version": "1.0.0-preview2-final",
"type": "build"
},
"Microsoft.NETCore.App": {
"version": "1.0.0"
},
"System.Runtime.InteropServices": "4.1.0",
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
},
"frameworks": {
"netcoreapp1.0": {
"imports": "dnxcore50"
}
},
"tools": {
"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
},
"runtimes": {
"win10-x64": {}
}
}
最佳答案
与EF6和SQLite一起使用时，我遇到了类似的问题。这个问题很突出，因为您尝试使用未用于关闭连接的，可用于选择以及更新操作的连接。尝试将本地DbContext与 using 关键字一起使用。这将在使用后处理dbContext。您至少将避免当前遇到的异常。SQLite中的另一个规则是，只有一个连接可以执行写操作。
因此，我们需要在执行任何其他操作之前确保写入连接已关闭，以使写入可用于其他连接。
关于c# - 数据库事务中的并发线程会导致.NET Core EF Core的严重延迟，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39622441/