CODEWITHSUNDEEP
sqliteentity-frameworkasp.net-coretransactions.net-8.0
Udontknow
Udontknow

Reputation: 1580

In-memory database does not like a two phase commit

My database-dependent code is mocked against an SQLite in-memory database, while production runtime can happen on environments such as Microsoft SQL Server, IBM Db2, Oracle Database, or Sybase.

I am trying to build a two-phase-commit procedure for an action log that gets saved when a database context associated with it is saved. Sometimes the changes happen on the same database where the action log tables reside. When I open transactions against an SQLite in-memory database and try to save context2 after saving context1, an exception is thrown after some 30 seconds:

SQLite Error 6: 'database table is locked'.

Connection string:

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

The test:

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();
   });
});

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;
   }
}

The two contexts change data on different entities, but still a collision shows up. I had to change the default IsolationLevel to ReadUncommited (a.k.a. "dirty read"), otherwise the lock would already occur on context2.Employees.ToList().

Is it a must to commit/rollback transactions to have subsequently started transactions handled even if they work on different entities?

Upvotes: 0

Views: 75

Answers (1)

Fildor
Fildor

Reputation: 16104

SQLite documentation > Transaction > Transactions > Read transactions versus write transaction says:

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

Emphasis mine. I guess this is what causes the problem.

Using SQLite as a test-implementation of another RDBMS has restrictions that the tester needs to be aware of. Where it cannot mock the behavior of the actual RDBMS it is recommended to use the same RDBMS as in production (a test instance, of course). There are tools to support this, for example containerization.

Upvotes: 2

Related Questions