sqlite - 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?

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.

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

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

Share Improve this question edited Feb 11 at 5:18 Zhi Lv 21.7k1 gold badge27 silver badges37 bronze badges asked Feb 4 at 13:23 Udontknow 1,58014 silver badges33 bronze badges

You should use the same database software in test that you use in production. – Shawn Commented Feb 4 at 15:07
Save tests like this for integration tests against a copy of a database(s) used in production, not substituting for in-memory. SQLLite is not guaranteed to be compatible with every other RDBMS and behaviour in EF is dependent on each the provider. For unit tests, mock "above" the DbContext and transactional low-level behaviour. – Steve Py Commented Feb 4 at 20:58
1 @Steve Py: Regarding "mocking above dbcontext": This can introduce enormous additional efforts and/or reduce test coverage. For example, making sure that a LINQ query / call against a DbSet is doing the right thing can be extremely tedious. – Udontknow Commented Feb 5 at 8:35
1 Mocking above the DbContext is generally done by mocking a class wrapping the data access via the DbContext/DbSets, commonly a Repository class or data service. Again, that would be for unit tests which test business logic code. For integration tests (does my code do what I expect with this actual data state) the main point is you should ideally be using the same database engine as you use in production since EF providers can have distinctly unique behaviours. – Steve Py Commented Feb 5 at 21:15

Add a comment |

1 Answer 1

Sorted by: Reset to default 2

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.

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

sqlite - In-memory database does not like a two phase commit - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)