TransactionScope with SQLite in-memory database and NHibernate

Question

I'm running into a problem where a transaction does not roll back when using TransactionScope.

We're using NHibernate with an in memory SQLite database, so that limits us to just one db connection for the duration of the entire lifetime of the application (in this case, some unit tests).

using (var ts = new TransactionScope(TransactionScopeOption.Required, 
                                     TimeSpan.Zero))
{
    using (var transaction = _repository.BeginTransaction())
    {
        _repository.Save(entity);
        transaction.Commit();
    }
    // ts.Complete(); <- commented Complete call still commits transaction
}

Even if I remove NHibernate's inner nested transaction so the code is simply as below, the transaction is still commited.

using (var ts = new TransactionScope(TransactionScopeOption.Required, 
                                     TimeSpan.Zero))
{       
    _repository.Save(entity);        
} // no Complete(), but the transaction still commits

Is it expecting a freshly opened SQLite connection inside the TransactionScope block in order to enlist it in the transaction?

Again, I can't supply it with a new connection because that would clear out the database.

Using NHibernate 3.0 and SQLite 1.0.66.0, both latest versions at the time of writing.

Note: using transaction.Rollback() on the NHibernate ITransaction object correctly rolls back the transaction, it's just the TransactionScope support that doesn't seem to work.

Does SQLite (and its driver) support distributed transactions, or is it explicitly aware of TransactionScope? — Diego Mijelshon
@diego: Yes, based on posts on the SQLite ADO.NET provider forums, TransactionScope has been supported for a few years now. "automatic distributed transaction enlistment" is also one of the features noted on their home page. — andreialecu
and if you check after the scope ends, is the data persisted? — Diego Mijelshon

andreialecu andreialecu · Accepted Answer · 2011-03-20T17:56:40

I think I may have found the reason for this. If the connection is not opened from inside the TransactionScope block, it will not be enlisted in the transaction.

There's some information here: http://msdn.microsoft.com/en-us/library/aa720033(v=vs.71).aspx

Solution:

I already had a .BeginTransaction() method in my repository, so I figured I'd manually enlist the connection in the ambient transaction there.

This is the code I ended up with:

    /// <summary>
    /// Begins an explicit transaction.
    /// </summary>
    /// <returns></returns>
    public ITransaction BeginTransaction()
    {
        if (System.Transactions.Transaction.Current != null)
        {
            ((DbConnection) Session.Connection).EnlistTransaction(System.Transactions.Transaction.Current);
        }
        return Session.BeginTransaction();
    }

And here's how I'm using it:

  using (var ts = new TransactionScope(TransactionScopeOption.Required, TimeSpan.Zero))
  using (var transaction = repository.BeginTransaction())
  {
       repository.Save(entity);
       transaction.Commit(); // nhibernate transaction is commited
        // ts.Complete(); // TransactionScope is not commited
  } // transaction is correctly rolled back now

TransactionScope with SQLite in-memory database and NHibernate

1 Answers