search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.72k stars 3.17k forks source link

SQLite Error 5: 'database is locked' during writing from two DB connections #29514

Open MaximMikhisor opened 1 year ago

MaximMikhisor commented 1 year ago

Model

public class RootEntity
{
    public Int32 Id { get; set; }

    public ChildEntity? Child { get; set; }
}

public class ChildEntity
{
    public Int32 Id { get; set; }

    public String? AProperty { get; set; }
}

Code

// Comment this whole task and application will run without any errors.
Task.Run(() =>
{
    using (var db1 = new ExampleContext())
    {
        db1.RootEntities.First().Child = new ChildEntity();

        db1.SaveChanges();
    }
});

using (var db2 = new ExampleContext())
{
    var rootEntities = db2.RootEntities.OrderBy(i => i.Id);

    foreach (var rootEntity in rootEntities)
    {        
        // Wait a bit until db1.SaveChanges() above will finish execution.
        Thread.Sleep(2000);

        rootEntity.Child = new ChildEntity();                 

        // Here will be an error Error: SQLite Error 5: 'database is locked'
        db2.SaveChanges();
    }
}

Project example

https://github.com/LineSmarts/SqliteBusyError

Steps to reproduce bug

  1. Open two connections to Sqlite database.
  2. Write data in first connection, and begin to read data in second connection (by IQueryable).
  3. Wait until first connection will be closed.
  4. Attemp to write data in second connection.
  5. SQLite Error 5: 'database is locked'

Expected behaviour

Unfortunately did not find proper description of busy_timeout logic on sqlite.org. Found below text on this site r-bloggers.com

If this timeout is set to a non-zero value, then the second connection will re-try the write operation several times, until it succeeds or the timeout expires.

Based on this description I expect that code above should not get any error during db2.SaveChanges(); execution because:

  1. There is no any other connection which could lock database at that moment
  2. busy_timeout is set to 30 second by default, this connection should re-try the write operation several times, and should succeed.

Include version information

Microsoft.Data.Sqlite version: 7.0.0 Target framework: (e.g. .NET 6.0) Operating system: Windows 10

ajcvickers commented 1 year ago

/cc @bricelam

ajcvickers commented 1 year ago

Notes for triage:

Logs:

dbug: 11/22/2022 10:56:22.446 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteNonQuery' (2ms).
dbug: 11/22/2022 10:56:22.455 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
info: 11/22/2022 10:56:22.474 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
dbug: 11/22/2022 10:56:22.584 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'Serializable'.
dbug: 11/22/2022 10:56:22.585 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 11/22/2022 10:56:22.585 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ChildEntity" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_ChildEntity" PRIMARY KEY AUTOINCREMENT,
          "AProperty" TEXT NULL
      );
info: 11/22/2022 10:56:22.585 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ChildEntity" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_ChildEntity" PRIMARY KEY AUTOINCREMENT,
          "AProperty" TEXT NULL
      );
dbug: 11/22/2022 10:56:22.586 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 11/22/2022 10:56:22.586 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "RootEntities" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_RootEntities" PRIMARY KEY AUTOINCREMENT,
          "ChildId" INTEGER NULL,
          CONSTRAINT "FK_RootEntities_ChildEntity_ChildId" FOREIGN KEY ("ChildId") REFERENCES "ChildEntity" ("Id")
      );
info: 11/22/2022 10:56:22.586 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "RootEntities" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_RootEntities" PRIMARY KEY AUTOINCREMENT,
          "ChildId" INTEGER NULL,
          CONSTRAINT "FK_RootEntities_ChildEntity_ChildId" FOREIGN KEY ("ChildId") REFERENCES "ChildEntity" ("Id")
      );
dbug: 11/22/2022 10:56:22.587 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 11/22/2022 10:56:22.587 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_RootEntities_ChildId" ON "RootEntities" ("ChildId");
info: 11/22/2022 10:56:22.587 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_RootEntities_ChildId" ON "RootEntities" ("ChildId");
dbug: 11/22/2022 10:56:22.592 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.
dbug: 11/22/2022 10:56:22.594 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Disposing transaction.
dbug: 11/22/2022 10:56:22.743 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'Serializable'.
dbug: 11/22/2022 10:56:22.745 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.749 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:22.752 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (5ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:22.768 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.768 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:22.769 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:22.769 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.769 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:22.770 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:22.770 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.770 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:22.771 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:22.771 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.771 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:22.772 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:22.774 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.
dbug: 11/22/2022 10:56:22.775 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Disposing transaction.
dbug: 11/22/2022 10:56:22.993 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.993 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:22.994 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      LIMIT 1
dbug: 11/22/2022 10:56:22.994 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      ORDER BY "r"."Id"
info: 11/22/2022 10:56:22.996 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      LIMIT 1
info: 11/22/2022 10:56:23.153 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (159ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      ORDER BY "r"."Id"
Waiting on 2
dbug: 11/22/2022 10:56:23.534 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Began transaction with isolation level 'Serializable'.
dbug: 11/22/2022 10:56:23.534 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:23.534 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p0=NULL], CommandType='Text', CommandTimeout='30']
      INSERT INTO "ChildEntity" ("AProperty")
      VALUES (@p0)
      RETURNING "Id";
info: 11/22/2022 10:56:23.534 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[@p0=NULL], CommandType='Text', CommandTimeout='30']
      INSERT INTO "ChildEntity" ("AProperty")
      VALUES (@p0)
      RETURNING "Id";
dbug: 11/22/2022 10:56:23.538 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command)
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 11/22/2022 10:56:23.538 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command)
      Executing DbCommand [Parameters=[@p2='1', @p1='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      UPDATE "RootEntities" SET "ChildId" = @p1
      WHERE "Id" = @p2
      RETURNING 1;
info: 11/22/2022 10:56:23.539 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[@p2='1', @p1='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      UPDATE "RootEntities" SET "ChildId" = @p1
      WHERE "Id" = @p2
      RETURNING 1;
dbug: 11/22/2022 10:56:23.542 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Committed transaction.
dbug: 11/22/2022 10:56:23.542 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction)
      Disposing transaction.
Done 1
Starting 2
Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters)
   at Microsoft.Data.Sqlite.SqliteTransaction..ctor(SqliteConnection connection, IsolationLevel isolationLevel, Boolean deferred)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel, Boolean deferred)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.ConnectionBeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__107_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at EfCoreNotNullNestedOwned.Program.Main(String[] args) in C:\local\code\repros\SqliteBusyError-master\SqliteBusyError-master\Program.cs:line 56

Process finished with exit code -532,462,766.

Updated code:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading;
using System.Threading.Channels;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace EfCoreNotNullNestedOwned
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new ExampleContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                for (int i = 0; i < 5; i++)
                    db.Add(new RootEntity());

                db.SaveChanges();
            }

            // Comment this whole task and application will run without any errors.
            Task.Run(() =>
            {
                using (var db1 = new ExampleContext())
                {
                    db1.RootEntities.First().Child = new ChildEntity();

                    Thread.Sleep(500);

                    db1.SaveChanges();
                }

                Console.WriteLine("Done 1");
            });         

            using (var db2 = new ExampleContext())
            {
                var rootEntities = db2.RootEntities.OrderBy(i => i.Id);

                foreach (var rootEntity in rootEntities)
                {
                    Console.WriteLine("Waiting on 2");
                    // Wait a bit until db1.SaveChanges() above will finish execution.
                    Thread.Sleep(2000);
                    Console.WriteLine("Starting 2");

                    rootEntity.Child = new ChildEntity();                 

                    // Here will be an error Error: SQLite Error 5: 'database is locked'
                    db2.SaveChanges();
                    Console.WriteLine("Done 2");
                }
            }
        }
    }

    public class ExampleContext : DbContext
    {
        public DbSet<RootEntity> RootEntities { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options
                .LogTo(Console.WriteLine, new[]
                {
                    RelationalEventId.TransactionStarted,
                    RelationalEventId.CommandCreated,
                    RelationalEventId.CommandExecuting,
                    RelationalEventId.CommandExecuted,
                    RelationalEventId.TransactionCommitted,
                    RelationalEventId.TransactionRolledBack,
                    RelationalEventId.TransactionDisposed,
                })
                .EnableSensitiveDataLogging()
                .UseSqlite($"Pooling=False;Data Source=blogging.db");
    }

    public class RootEntity
    {
        public Int32 Id { get; set; }

        public ChildEntity? Child { get; set; }
    }

    public class ChildEntity
    {
        public Int32 Id { get; set; }

        public String? AProperty { get; set; }
    }
}
Gibrid89 commented 1 year ago

I am using this extension to fix this error. It is a sad that there is no normal solution.

public static class SQLiteThreadSaveExtension
  {
      public static ReaderWriterLockSlim DBReaderWriterLock { get; private set; } = new ReaderWriterLockSlim();

      public static void SaveChangesConcurrent(this MyDBContext context)
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              context.SaveChanges();
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task SaveChangesConcurrentAsync(this MyDBContext context)
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              await context.SaveChangesAsync();
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static void BulkInsertConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              context.BulkInsert(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task BulkInsertConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              await context.BulkInsertAsync(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static void BulkUpdateConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              context.BulkUpdate(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task BulkUpdateConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              await context.BulkUpdateAsync(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static void BulkDeleteConcurrent<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              context.BulkDelete(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task BulkDeleteConcurrentAsync<T>(this MyDBContext context, IList<T> entities, BulkConfig? bulkConfig = null, Action<decimal>? progress = null, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              await context.BulkDeleteAsync(entities, bulkConfig, progress, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static int BatchUpdateConcurrent<T>(this IQueryable<T> query, Expression<Func<T, T>> updateExpression, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              return query.BatchUpdate(updateExpression, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static int BatchDeleteConcurrent<T>(this IQueryable<T> query) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              return query.BatchDelete();
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task<int> BatchUpdateConcurrentAsync<T>(this IQueryable<T> query, Expression<Func<T, T>> updateExpression, Type? type = null) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              return await query.BatchUpdateAsync(updateExpression, type);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static async Task<int> BatchDeleteConcurrentAsync<T>(this IQueryable<T> query) where T : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              return await query.BatchDeleteAsync();
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static IDbContextTransaction BeginTransactionConcurrent(this DatabaseFacade database) 
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              return database.BeginTransaction();
          }
          catch
          {
              DBReaderWriterLock.ExitWriteLock();
              throw;
          }
      }
      public static void CommitConcurrent(this IDbContextTransaction transaction)
      {
          try
          {
              transaction.Commit();
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static void TruncateConcurrent<TEntity>(this DbSet<TEntity> dbSet, MyDBContext context) where TEntity : class
      {
          try
          {
              DBReaderWriterLock.EnterWriteLock();
              dbSet.Truncate(context);
          }
          finally
          {
              DBReaderWriterLock.ExitWriteLock();
          }
      }

      public static void Truncate<TEntity>(this DbSet<TEntity> dbSet, MyDBContext context) where TEntity : class
      {
          if (dbSet is null)
              throw new ArgumentNullException(nameof(dbSet));

          var tableName = context.GetTableName(typeof(TEntity));
          context.Database.ExecuteSqlRaw($"DELETE FROM \"{tableName}\"");
      }
  }
albyrock87 commented 6 months ago

Have anyone managed to workaround this? I think it's really critical.

I also tried to use ReaderWriterLockSlim as follows, but even if calls are serialized in the right way, I still get the database locked issue.

It doesn't seem a parallelism problem, it seems to me that something has not been released on the database after writing.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;

namespace EfCoreNotNullNestedOwned
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new ExampleContext())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                for (int i = 0; i < 5; i++)
                    db.Add(new RootEntity());

                db.SaveChanges();
            }

            // Comment this whole task and application will run without any errors.
            var task = Task.Run(() =>
            {
                using var db1 = new ExampleContext();
                db1.RootEntities.First().Child = new ChildEntity();
                db1.SaveChanges();
                Console.WriteLine("Done 1");
            });

            using (var db2 = new ExampleContext())
            {
                var rootEntities = db2.RootEntities.OrderBy(i => i.Id);
                foreach (var rootEntity in rootEntities)
                {
                    Console.WriteLine("Waiting on 2");
                    // Wait a bit until db1.SaveChanges() above will finish execution.
                    task.Wait();

                    Console.WriteLine("Starting 2");

                    rootEntity.Child = new ChildEntity();                 

                    // Here will be an error Error: SQLite Error 5: 'database is locked'
                    db2.SaveChanges();

                    Console.WriteLine("Done 2");
                }
            }
        }
    }

    public class SynchronizedEntityQueryProvider(IQueryCompiler queryCompiler) : EntityQueryProvider(queryCompiler)
    {
        public override object Execute(Expression expression)
        {
            ExampleContext.Lock.EnterReadLock();
            try
            {
                return base.Execute(expression);
            }
            finally
            {
                ExampleContext.Lock.ExitReadLock();
            }
        }

        public override TResult Execute<TResult>(Expression expression)
        {
            ExampleContext.Lock.EnterReadLock();
            try
            {
                return base.Execute<TResult>(expression);
            }
            finally
            {
                ExampleContext.Lock.ExitReadLock();
            }
        }

        public override TResult ExecuteAsync<TResult>(Expression expression, CancellationToken cancellationToken = new CancellationToken())
        {
            ExampleContext.Lock.EnterReadLock();
            try
            {
                return base.ExecuteAsync<TResult>(expression, cancellationToken);
            }
            finally
            {
                ExampleContext.Lock.ExitReadLock();
            }
        }
    }

    public class ExampleContext : DbContext
    {
        public static readonly ReaderWriterLockSlim Lock = new();
        public DbSet<RootEntity> RootEntities { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
        {
            options
                .UseSqlite($"Data Source=blogging.db")
                .LogTo(Console.WriteLine, new[]
                {
                    RelationalEventId.TransactionStarted,
                    RelationalEventId.CommandCreated,
                    RelationalEventId.CommandExecuting,
                    RelationalEventId.CommandExecuted,
                    RelationalEventId.TransactionCommitted,
                    RelationalEventId.TransactionRolledBack,
                    RelationalEventId.TransactionDisposed,
                })
                .EnableSensitiveDataLogging()
                .ReplaceService<IAsyncQueryProvider, SynchronizedEntityQueryProvider>();
        }

        public override int SaveChanges()
        {
            Lock.EnterWriteLock();
            try
            {
                return base.SaveChanges();
            }
            finally
            {
                Lock.ExitWriteLock();
            }
        }

        public override void Dispose()
        {
            Database.CloseConnection();
            base.Dispose();
        }
    }

    public class RootEntity
    {
        public Int32 Id { get; set; }

        public ChildEntity? Child { get; set; }
    }

    public class ChildEntity
    {
        public Int32 Id { get; set; }

        public String? AProperty { get; set; }
    }
}

Logs:

dbug: 16/04/2024 10:34:21.720 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 16/04/2024 10:34:21.724 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
info: 16/04/2024 10:34:21.730 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA journal_mode = 'wal';
dbug: 16/04/2024 10:34:21.756 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Began transaction with isolation level 'Serializable'.
dbug: 16/04/2024 10:34:21.756 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 16/04/2024 10:34:21.756 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ChildEntity" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_ChildEntity" PRIMARY KEY AUTOINCREMENT,
          "AProperty" TEXT NULL
      );
info: 16/04/2024 10:34:21.757 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "ChildEntity" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_ChildEntity" PRIMARY KEY AUTOINCREMENT,
          "AProperty" TEXT NULL
      );
dbug: 16/04/2024 10:34:21.757 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 16/04/2024 10:34:21.757 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "RootEntities" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_RootEntities" PRIMARY KEY AUTOINCREMENT,
          "ChildId" INTEGER NULL,
          CONSTRAINT "FK_RootEntities_ChildEntity_ChildId" FOREIGN KEY ("ChildId") REFERENCES "ChildEntity" ("Id")
      );
info: 16/04/2024 10:34:21.757 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "RootEntities" (
          "Id" INTEGER NOT NULL CONSTRAINT "PK_RootEntities" PRIMARY KEY AUTOINCREMENT,
          "ChildId" INTEGER NULL,
          CONSTRAINT "FK_RootEntities_ChildEntity_ChildId" FOREIGN KEY ("ChildId") REFERENCES "ChildEntity" ("Id")
      );
dbug: 16/04/2024 10:34:21.757 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteNonQuery' (0ms).
dbug: 16/04/2024 10:34:21.757 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_RootEntities_ChildId" ON "RootEntities" ("ChildId");
info: 16/04/2024 10:34:21.757 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_RootEntities_ChildId" ON "RootEntities" ("ChildId");
dbug: 16/04/2024 10:34:21.758 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Committed transaction.
dbug: 16/04/2024 10:34:21.758 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Disposing transaction.
dbug: 16/04/2024 10:34:21.797 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Began transaction with isolation level 'Serializable'.
dbug: 16/04/2024 10:34:21.798 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.799 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.800 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.805 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p0=NULL (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "RootEntities" ("ChildId")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.805 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Committed transaction.
dbug: 16/04/2024 10:34:21.805 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Disposing transaction.
dbug: 16/04/2024 10:34:21.880 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.880 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      ORDER BY "r"."Id"
info: 16/04/2024 10:34:21.880 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      ORDER BY "r"."Id"
dbug: 16/04/2024 10:34:21.880 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.880 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      LIMIT 1
info: 16/04/2024 10:34:21.880 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "r"."Id", "r"."ChildId"
      FROM "RootEntities" AS "r"
      LIMIT 1
Waiting on 2
dbug: 16/04/2024 10:34:21.888 RelationalEventId.TransactionStarted[20200] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Began transaction with isolation level 'Serializable'.
dbug: 16/04/2024 10:34:21.888 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.888 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p0=NULL], CommandType='Text', CommandTimeout='30']
      INSERT INTO "ChildEntity" ("AProperty")
      VALUES (@p0)
      RETURNING "Id";
info: 16/04/2024 10:34:21.888 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p0=NULL], CommandType='Text', CommandTimeout='30']
      INSERT INTO "ChildEntity" ("AProperty")
      VALUES (@p0)
      RETURNING "Id";
dbug: 16/04/2024 10:34:21.889 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (0ms).
dbug: 16/04/2024 10:34:21.889 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@p2='1', @p1='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      UPDATE "RootEntities" SET "ChildId" = @p1
      WHERE "Id" = @p2
      RETURNING 1;
info: 16/04/2024 10:34:21.889 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (0ms) [Parameters=[@p2='1', @p1='1' (Nullable = true)], CommandType='Text', CommandTimeout='30']
      UPDATE "RootEntities" SET "ChildId" = @p1
      WHERE "Id" = @p2
      RETURNING 1;
dbug: 16/04/2024 10:34:21.890 RelationalEventId.TransactionCommitted[20202] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Committed transaction.
dbug: 16/04/2024 10:34:21.890 RelationalEventId.TransactionDisposed[20204] (Microsoft.EntityFrameworkCore.Database.Transaction) 
      Disposing transaction.
Done 1
Starting 2
Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 5: 'database is locked'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters)
   at Microsoft.Data.Sqlite.SqliteTransaction..ctor(SqliteConnection connection, IsolationLevel isolationLevel, Boolean deferred)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel, Boolean deferred)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.Data.Sqlite.SqliteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.ConnectionBeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__112_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
albyrock87 commented 6 months ago

@ajcvickers @MaximMikhisor I partially understood what's happening here.

Now, I think this is a matter of "Isolation Level". If C1 is reading and writing from the table (and it is the only one doing that) the connection can access the latest state, and therefore can always perform an update. If someone acts in the middle C2, now C1 must complete what it's doing in order to "access" the updated state and ensure consistent writes.

I think this narrows down to how WAL works by using these checkpoints https://www.sqlite.org/wal.html

So my suggestion, in general, is to never write while reading from the same db context, and maybe avoid lazy evaluation of enumerables (simply use ToList).

MaximMikhisor commented 5 months ago

@albyrock87

Inside loop we have "Thread.Sleep(2000)" It means that when C1 is trying to write to DB, C2 already closed and should not influence on C1. But instead we have an exception. Are you agree?

at this point C1 is still reading the table and it has to finish reading the data before being able to write

Who "being able to write"? C1 or C2?

albyrock87 commented 5 months ago

@MaximMikhisor I'm saying C1 is "auto blocking itself" due to the fact it is still reading (foreach on IEnumerable) from an old checkpoint, while writing requires access to the latest checkpoint.

MaximMikhisor commented 5 months ago

If you comment or delete task, i.e. if only C2 will work, you will see that C2 is working ok. Even with db2.SaveChanges(); inside of the foreach.

albyrock87 commented 5 months ago

@MaximMikhisor exactly, that's because no one changed the checkpoint state in the mean time, so it can always write and read from the latest checkpoint.

At that poi C1 is the only one doing writes and updating the checkpoint, so he knows where to write.

This is the only explanation I could come up with.

MaximMikhisor commented 5 months ago

You a bit confusing "checkpoint" word. From documentation: Moving the WAL file transactions back into the database is called a "checkpoint".

Checkpoint is not source of problem.

I guess I found what is a source of "SQLite Error 5: 'database is locked'" error: SQLITE_BUSY_SNAPSHOT

The SQLITE_BUSY_SNAPSHOT error code is an extended error code for SQLITE_BUSY that occurs on WAL mode databases when a database connection tries to promote a read transaction into a write transaction but finds that another database connection has already written to the database and thus invalidated prior reads.

But for some reason SQLite return "SQLite Error 5: 'database is locked'" instead of "(517)SQLITE_BUSY_SNAPSHOT" error.

I think ticket could be closed. Because it is not a bug of EFCore, but it is expected behavior of SQLite.

albyrock87 commented 5 months ago

@MaximMikhisor you're right I switched words between "checkpoint" and "snapshot", but still that's the concept I wanted to explain: if someone writes while a connection is actively reading a snapshot (which I called checkpoint above) then that connection cannot write until it completes the read, because otherwise it would try to write to an old snapshot.

Anyway, as we both mentioned this is a SQLite expected behavior, so we cannot do anything about it.