Unhandled handling when using Entity Framework Core with SQLite in .NET 6, 'database is locked' scenarios
I'm stuck on something that should probably be simple. I need some guidance on I'm working with an scenario while using Entity Framework Core (version 6.0.0) with SQLite in my .NET 6 application... The question arises when I try to perform a bulk insert operation using `AddRangeAsync()` on a DbSet, and I get the following exception: ``` Unhandled exception: SQLiteException: database is locked ``` This happens sporadically but frequently when multiple threads attempt to insert records concurrently. Here’s a snippet of the relevant code: ```csharp public async Task AddItemsAsync(List<Item> items) { using (var context = new MyDbContext()) { await context.Items.AddRangeAsync(items); await context.SaveChangesAsync(); } } ``` I've tried wrapping the `AddItemsAsync` call within a lock statement to prevent concurrent access, but it didn't resolve the scenario. I also ensured that the database connection is not shared between threads, as each `MyDbContext` instance should have its own connection. Another approach I considered was using a `TransactionScope`, but I got the same locking behavior: ```csharp using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)) { await AddItemsAsync(items); scope.Complete(); } ``` I’ve checked the SQLite settings and made sure that `PRAGMA synchronous` is set to `NORMAL`, but the scenario continues. How can I resolve this locking scenario? Is there a best practice for handling concurrent inserts in SQLite with EF Core that I might be missing? Any insights would be greatly appreciated! My team is using C# for this desktop app. Is there a better approach? This is for a microservice running on CentOS. What would be the recommended way to handle this?