Why doesn't AbortTransaction work?

Question

I am using npoco, trying to work with transactions, I can´t get the AbortTransaction method to rollback anything in my db.

public class ItemRepository
{
  private Func<Database> _db;

  public ItemRepository(Func<Database> db)
  {
        _db = db;
  }

Public void Update(){
    using (_db().Transaction)
    {
        _db().BeginTransaction();
        foreach (var item in itemToUpdate.Items)
        { 
            _db().Insert(item);
            if (SomethingIsNotCorrect())
            {
                _db().AbortTransaction();
            }
        }
    _db().CompleteTransaction();
    }
}}

Call from test class:

_db = () => new Database(String.Format("DataSource={0};Persist Security Info=False;", DbPath),"System.Data.SqlServerCe.4.0");
_itemRepository = new ItemRepository(() => _db());
_itemRepository.Update();

------------- edit after suggested answer:

var db = _db();
using (db.Transaction)
{
    db.BeginTransaction();  
    foreach (var item in itemToUpdate.Items)
    {
        db.Insert(item);
        db.Transaction.Commit();
        if (GetNutrientConns(itemToUpdate).Count > 2)
        {
             db.AbortTransaction();
        }
    }  
    db.CompleteTransaction();
}

Now I must use db.Transaction.Commit() to have something inserting in db. Then when trying to run db.AbortTransaction() I get error message:

"This SqlCeTransaction has completed; it is no longer usable."

What _db() is? Dont say it is method that returns new DB connection — Renatas M., Oct 29 '14 at 13:02
@Reniuz, private Func _db; setting this in constructor one time so should be same.. — daniel_aren, Oct 29 '14 at 13:08
@daniel_aren I've never used this library before, but I strongly suspect you're supposed to call that once, store the result in a field or variable, and refer to it repeatedly. — Asad Saeeduddin, Oct 29 '14 at 13:10
Actually, could you please show us all of your code that is part of the call graph here, so we're not guessing? Please include the constructor and any fields you're referring to here. — Asad Saeeduddin, Oct 29 '14 at 13:11
@Asad, if you refer to the _db, it´s set once in constructor.. public ItemRepository(Func db) { _db = db; } — daniel_aren, Oct 29 '14 at 13:14
That's not a `Database` instance you're accepting in your constructor. It's a function that *returns* a database instance. Whether it's producing a new one or returning the same one over and over is opaque to us (and apparently to you as well, since you're accepting it in your constructor). Make a field of type `IDatabase` or `Database` or whatever, and store `_db = db()` in your constructor. Also, please rename that `Func db` parameter you're accepting to `dbFactory` or something, because that's what that is. — Asad Saeeduddin, Oct 29 '14 at 13:16
@Reniuz That's just good practice. A data context shouldn't be reused across multiple logical transactions, so you should accept a data context *factory* instead of a data context, in order to ensure the data context backing your repository is born and dies with it. — Asad Saeeduddin, Oct 29 '14 at 13:20
@Asad, thanks for input about structure but could that be problem here with transaction problem? — daniel_aren, Oct 29 '14 at 13:31
@daniel_aren Yes. That is the problem because you create a new `Database` instance with every invocation of `_db()`, and none of those have any idea what transaction related calls you've invoked on the previous instances. — Asad Saeeduddin, Oct 29 '14 at 13:34
@Asad, now the Insert(item) doesn´t happen until i call db.CompleteTransaction().. and then I can´t make rollback/abort.. — daniel_aren, Oct 29 '14 at 14:02
@daniel_aren That is the point of transactions. Either all constituent actions happen, or nothing does. — Asad Saeeduddin, Oct 29 '14 at 14:35

Asad Saeeduddin · Answer 1 · 2014-10-29T13:38:31.620

2

From your update, it is pretty clear what the problem is. You're creating a factory function that instantiates and returns a new connection/datacontext, and passing that into your repository constructor. So far so good.

You're then proceeding to invoke every single transaction related call on a fresh new Database instance that you get from your factory function, which makes no sense. In your constructor, use your factory function to actually get a Database instance, and store that in a field. Use that as many times as necessary.

public class ItemRepository
{
    private Database _db;

    public ItemRepository(Func<Database> dbFactory)
    {
        _db = dbFactory();
    }

    ...
}

UPDATE: As pointed out by Mike C, in the interests of further limiting the scope and lifetime of a datacontext, it would be preferable to store the factory and create a Database instance that lives within the scope of the Update method, like so:

public class ItemRepository
{
    private Func<Database> _dbFactory;

    public ItemRepository(Func<Database> dbFactory)
    {
        _dbFactory = dbFactory;
    }

    public void Update() {
        var db = _dbFactory();

        // Now use db wherever you were using _db()
        ...
    }
}

edited Oct 29 '14 at 13:38

answered Oct 29 '14 at 13:31

Asad Saeeduddin

46,193
6
90
139

1

Alternatively, the OP could simply call the factory method at the beginning of each method and use the returned instance throughout the call. This would help ensure thread safety as no two calls to `Update` could share the same instance. – mclark1129 Oct 29 '14 at 13:33
@MikeC That's a fair point. Either way, there shouldn't be more than one call to the factory function per call to `Update` method. – Asad Saeeduddin Oct 29 '14 at 13:35
@Asas, I am sorry but this doesn´t help.. I need to understand how npoco works with transactions.. – daniel_aren Oct 29 '14 at 15:23
@daniel It looks like you've fundamentally misunderstood what a database transaction is. A transaction allows you to atomically group a bunch of database interactions, with the ability to set them all in stone by committing the transaction or reverting everything since the beginning of a transaction by rolling back. You can't rollback a committed transaction. This makes no sense, and has nothing to do with npoco – Asad Saeeduddin Oct 29 '14 at 15:59
@daniel_aren You might be looking to "preview" the effects of a transaction without committing, but that has to do with isolation levels in your db of choice, and again, nothing to do with npoco or the code you showed us. – Asad Saeeduddin Oct 29 '14 at 16:05
@Asad, I was hoping to do tests with isolation level read uncommited and be able to see what db.Insert() was saving to db and then doing a rollback to see it dissapear but no success, another question perhaps.. thanks for all help though. – daniel_aren Oct 30 '14 at 10:16

score 0 · Accepted Answer · answered Oct 30 '14 at 10:28

Final solution update method:

var db = _dbFactory();
public void Update() {
try
{
    db.BeginTransaction();
    foreach(item in itemlist)
    {
        db.Insert(item);
    }
    db.CompleteTransaction();
}
catch(Exception)
{
    db.Transaction.Rollback();
}

I am not sure if Rollback are needed in exception because when I testing with throwing exception the transaction dissapears.

Why doesn't AbortTransaction work?

2 Answers2