Sqlite In-memory database does not like a two phase commit

Question

Our db-dependent code is mocked against a Sqlite in-memory DB (while the actual production runtime can happen on quite different environments such as MSSql, DB2, Oracle, Sybase).

I am trying to build up a two-phase-commit procedure (for an action log that get's saved when a database context associated with it is saved). Sometimes, the changes to protocolized happen on the same database where the actionlog tables reside. This is where the trouble shows up.

For some reason, as soon as I open two transactions against a Sqlite-InMemory-DB in two different contexts and try to save context2 after saving context1, an exception is thrown after some 30 seconds: "SQLite Error 6: 'database table is locked'."

This is the connection string used:

  var connectionString = $"Data Source=file:{DatabaseAlias};mode=memory;cache=shared";
  optionsBuilder.UseSqlite(connectionString);

This is the test I set up:

var context1 = factory.CreateDbContext();
var context2 = factory.CreateDbContext();
context1.WithTransaction(() =>
{
   var keysFromContext1 = context1.Keys.ToList();
   keysFromContext1.First().Name = "Changed First";
   context2.WithTransaction(() =>
   {
      var employeesFromContext2 = context2.Employees.ToList();
      employeesFromContext2.First().Name = "Changed Second";
      context1.SaveChanges();
      context2.SaveChanges();
   });
});

This is the code for the transaction handling:

  public void WithTransaction(Action action)
  {
     WithTransaction(action, IsolationLevel.ReadUncommitted);
  }

  public void WithTransaction(Action action, IsolationLevel isolationLevel)
  {
     if (Database.CurrentTransaction != null)
     {
        action();
        return;
     }
     Database.BeginTransaction(isolationLevel);
     try
     {
        action();
        Database.CommitTransaction();
     }
     catch
     {
        Database.RollbackTransaction();
        throw;
     }
  }

As you can see, the two contexts change data on different entities, but still, a collision shows up.

I also had to change the default IsolationLevel to ReadUncommited (a.k.a. "Dirty read"), otherwise the block would already occur on loading the data via the context2.Employees.ToList() call.

Is Sqlite not able to handle multiple transactions at a time, is it a must to commit / rollback transactions in order to have subsequently started transactions handled, even if they work on completely different entities? Are there always central locks effectively preventing two-phase-commits on the same database?

Answer 1

Section 2.1 of the Documentation sais:

SQLite supports multiple simultaneous read transactions coming from separate database connections, possibly in separate threads or processes, but only one simultaneous write transaction.

(Emphasis by me)

I am guessing this is what is causing the problem for you.

As stated in comments, using SQLite as a "Test"-Implementation of an RDBMS has certain restrictions that the tester needs to be fully aware of and consider in the tests.

Where it cannot mock the behavior of the actual RDBMS in use, it is mostly recommended to use the same RDBMS as in production (a test instance, of course).
Nowadays, there are many tools to support this, for example by using containerization.

---

For reference: A Microsoft Article on the matter is

Use a database server running as a container
learn.microsoft article from 2022-09-21