Efcore: 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
````cs
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 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.