Concurrent threads in DB transaction cause major delay in .NET Core EF Core

Question

Background

I am trying to write an async server that works with SQLite DB. I am using .NET Core with Entity Framework Core.

I am using UnitOfWork with GenericRepository patterns, but as my sample code below shows, this is not really related to these patterns.

I am using Windows 10, but I would expect any supported .NET Core platform to behave the same.

What I want to Achieve

What I want to achieve is a reasonable transaction behavior. I have reduced the whole problem to a simple scenario, in which I look into the database if a certain object exists and if not I add it, otherwise I fail. This whole operation is in a transaction and the expected scenarios are:

One

1. The object does not exist in the database.
2. Thread 1 checks whether the object exists in the database ands sees that it does not exist.
3. Thread 1 adds the object to the database.
4. Object exists in the database.

Two

1. The object exists in the database.
2. Thread 1 checks whether the object exists in the database and sees that it exists.
3. Thread 1 reports failure because the object exists.
4. Object still exists in the database.

Three

1. The object does not exist in the database.
2. Thread 1 checks whether the object exists in the database and sees that it does not exist.
3. Thread 2 checks whether the object exists in the database and sees that it does not exist.
4. Thread 1 attempts to add the object to the database.
5. Thread 2 attempts to add the object to the database.
6. Either thread 1 or thread 2 succeeds in adding the object to the database, the other thread fails because of the transaction constraints.
7. Object exists in the database.

Obviously, scenarios One and Two work perfectly, because there is only a single thread operating. The problem is with the number Three.

The Problem

The problem is that when in situation Three we enter step 5, the whole thing blows up. There is a 30 second hang for both threads and most of the time neither of the threads manages to add the object to the database.

I know how to solve this easily with a global application lock, but I would like to know if it is possible to solve this without locking and thus preserving async/await functionality for the database access.

Sometimes, one thread manages to add the object, the other thread fails, but even then it takes those 30 seconds for both threads to complete the operation, which is entirely unusable.

Sample Output

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.

Code

I've tried to cut off everything not related to keep this minimal. If you want to run the program, just create these files in Visual Studio, wait for .NET Core project sync, compile the project, run "add-migration first" and "update-database" to create the database and you can run it. Without Visual Studio, you need to use "dotnet" and "dotnet ef" commands.

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": {}
  }
}

Answer 1

I faced similar issue while using it with EF6 and SQLite. The issue is prominent because you are trying to use connection which is reused for select as well as update operations without doing connection close. Try to use local DbContext with using keyword. This will dispose the dbContext after its use. You will at least avoid the exception you are getting currently.

Another rule in SQLite is, Only one connection can do the write operation.

So we need to make sure the writing connection is closed before doing any other operation in order to make the write available to other connections.