Concurrent Write on SQLite with single connection

[YZahringer]

I try to use a single open connection with SQLite database and avoid opening it with each instance of DbContext to optimize access, this has a big impact with a encrypted database.

It works well with read operations but causes errors with concurrent/parallel write operations:

• SQLite Error 1: 'cannot rollback - no transaction is active'.
• SQLite Error 1: 'cannot start a transaction within a transaction'.

A single connection with concurrent writes is not supported by EF Core? Forced to create and open new connection, with performance loss, to manage concurrent writes?

Related to #16234?

Steps to reproduce

https://github.com/MADSENSE/Madsense.EFCore.Tests/tree/sqlite-open-connection

public class ConcurrentDbContextTests
{
    [Theory]
    [InlineData(ServiceLifetime.Singleton, false, 1, 100)]
    [InlineData(ServiceLifetime.Transient, false, 3, 100)]
    [InlineData(ServiceLifetime.Singleton, false, 3, 100)] // SQLite Error 1: 'cannot rollback - no transaction is active'.
    [InlineData(ServiceLifetime.Transient, true, 3, 100)]
    [InlineData(ServiceLifetime.Singleton, true, 3, 100)] // SQLite Error 1: 'cannot start a transaction within a transaction'.
    public async Task Sqlite_ConcurrentSave_Test(ServiceLifetime optionsLifeTime, bool openConnection, int concurrentSaveCount, int insertOperationsCount)
    {
        // Prepare
        var serviceCollection = new ServiceCollection();
        var connectionString = $"Filename={Guid.NewGuid()}.db";

        serviceCollection.AddDbContext<AppContext>((s, builder) =>
        {
            var connection = new SqliteConnection(connectionString);

            if (openConnection)
                connection.Open();

            builder.UseSqlite(connection);
        }, ServiceLifetime.Transient, optionsLifeTime);
        var serviceProvider = serviceCollection.BuildServiceProvider();

        await using (var initContext = serviceProvider.GetRequiredService<AppContext>())
        {
            await initContext.Database.EnsureCreatedAsync();
        }

        // Act
        var addDataFunc = new Func<Task>(async () =>
        {
            for (var i = 0; i < insertOperationsCount; i++)
            {
                await using var context = serviceProvider.GetRequiredService<AppContext>();
                {
                    await context.AddAsync(new BasicModel{Name = Guid.NewGuid().ToString()});
                    await context.SaveChangesAsync();
                }
            }
        });

        var concurrentTasks = Enumerable.Range(0, concurrentSaveCount).Select(i => Task.Run(() => addDataFunc()));
        await Task.WhenAll(concurrentTasks);
        
        // Assert
        await using var assertContext = serviceProvider.GetRequiredService<AppContext>();
        Assert.Equal(concurrentSaveCount*insertOperationsCount, assertContext.BasicModels.Count());
    }
}

public class AppContext : DbContext
{
    public DbSet<BasicModel> BasicModels { get; protected set; }

    public AppContext(DbContextOptions<AppContext> options)
        : base(options)
    {

    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<BasicModel>(entity =>
        {
            entity.HasKey(e => e.Id);
        });
    }
}

public class BasicModel
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Further technical details

EF Core version: 3.1.8
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: netcoreapp3.1
Operating system: Windows 10 2004
IDE: Visual Studio 2019 16.7.3

[bricelam]

SQLite connections (and most database connections for that matter) are not thread safe. You'll need one connection per thread.

Duplicate of #13837

[YZahringer]

@bricelam Thank you for the answer.

The connection is not thread safe for all operations? or only for write operations? If is only write operations, maybe I can consider a lock on SaveChanges?

Is there a solution to optimize connections to an encrypted Sqlite database? If I make a new connection for each new DbContext, the performance is really bad (~70x slower than unencrypted).

[roji]

@bricelam is the authority here, but like all ADO.NET providers, the connection object isn't thread-safe for any operations (it internally manages state and does not lock against concurrent use).

As written above, the solution here would be to support connection pooling (#13837) just like most other database provider - opening and closing would mean allocating and returning physical connection objects to an internally-managed pool.

In the meantime, you can implement some basic pooling yourself in your application - it should be too hard. I'd take a look at ObjectPool - instead of instantiating a new SqliteConnection every time a context is created, you'd allocate one from the ObjectPool.

[YZahringer]

Thanks for the details guys 👍

I hope that #13837 will be fixed soon, currently the performance on an encrypted database is really bad. I use EF Core SQLite with Xamarin on mobile app, so the performance is more degraded 😕

I think this is may be related to #19588 and improve performance on all platforms.

[MacKey-255]

I'm having this same problem. When I try to use DbContext.SaveChanges() concurrently (on a website). It basically throws me this error:

System.InvalidOperationException: SqliteConnection does not support nested transactions.
   en Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel)
   en Microsoft.Data.Sqlite.SqliteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   en System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
   en Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   en Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction()
   en Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   en Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   en Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   en Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   en Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   en Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   en Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   en Microsoft.EntityFrameworkCore.DbContext.SaveChanges()

I have a single connection which is shared by each DbContext created by request. Everything works fine when it comes to reading information but in writing it throws me the error: SqliteConnection does not support nested transactions. What can i do to fix it?
SAlu2s

Further technical details

Microsoft.EntityFrameworkCore 3.1.14
Microsoft.EntityFrameworkCore.Design 3.1.14
Microsoft.EntityFrameworkCore.Sqlite.Core 3.1.14
SQLitePCLRaw.bundle_e_sqlcipher 2.0.4