Litedb: Feature request - Transaction support in 4.0

[RytisLT]

Without the transaction support I cannot use Lite DB anymore. I think it will break a lot of projects. Can you please add Transaction support back to 4.0 and/or explain why it was removed in the first place?

[mbdavid]

Hi @RytisLT,

Transaction has a big issue in all LiteDB versions: keeps datafile locked with no close guarantee. When I re-write all lock control system to v4 to solve concurrency problem (Unable to cast object of type 'LiteDB.EmptyPage' to type 'LiteDB.HeaderPage') I tried to keep as simple as possible: removing transaction, external journal file, adding find cursor, .... all this has same goal: keeps lock expose only inside engine class.

It´s not first time that I remove transaction and then add back again, and get more problems. That's why it´s important.

But I'm still thinking in how offer a transaction-like operation to user, but keeps inside a single method call. My first idea was create a "batch operation", like you can create a package with N operations and run once.

Your transaction needs are about "do all or nothing" or it's more "only a single person can do this at time"?

[RytisLT]

Hi, my use case is "do all or nothing" across multiple collections

[mbdavid]

Hi @RytisLT, is this case a bulk operation can be a solution for you? Here some "dream-code" about this:

```C#
// engine version (non-strong typed)
var bulk = db.Bulk();

bulk.Insert("col1", bsonDoc1);
bulk.Insert("col1", bsonDoc2);
bulk.Insert("col2", bsonDoc3);

bulk.Update("col8", bsonDoc5);

bulk.Update("col1", Query.EQ("Age", 22), Update.Set("Age", "$.Age+1"))

bulk.Delete("col2", Query.EQ("Age", 22));

// run all stack operations inside a single transaction (ACID)
bulk.Execute();
```

As I mentioned before, in this situation I'm keeping all database operations inside a single transaction but without keeps locked datafile after use.
It's just a code preview, must missing how track reference between documents (like if bsonDoc5 needs bsonDoc1 id from auto-id).

[RytisLT]

looks good, If any of those Inserts would produce a duplicate key exception when would it fail? By insert or Execute()?

[thecodrr]

@mbdavid Currently I am using LiteDB transactions throughout my code but as they are not included in version 4.0, I cannot upgrade. Please re-implement the support for transactions. Thanks.

[MichaelRumpler]

I have a unittest which writes a pretty big db which results in a file of 48MB. In version 3 I used a transaction to write the whole database.

In v3.1.4 the test with the transaction takes 7 seconds.
When I remove the transaction and run the same test in LiteDB 3.1.4, it takes 98 seconds.
After updating LiteDB to 4.0.0 the test takes 400 seconds.

The only thing I changed in my code is that I removed the transaction when saving the whole file.
Old:

        using (var liteDatabase = new LiteDatabase(connectionString))
        {
            using (var tran = liteDatabase.BeginTrans())
            {
                var items = liteDatabase.GetCollection("items");
                Save(items, database.Root);
                tran.Commit();
            }
        }

New:

        using (var liteDatabase = new LiteDatabase(connectionString))
        {
            var items = liteDatabase.GetCollection("items");
            Save(items, database.Root);
        }

The Save method iterates over our item tree, checks for each changed item, whether it was already in the current file (with FindById) and calls Insert or Update respectively.

    private void Save(LiteCollection<BsonDocument> items, ItemBase item)
    {
        if (item.IsModified)
        {
            item.IsModified = false;
            var f = items.FindById(item.Id);
            if (f == null)
                items.Insert(Convert(item, f));
            else
                items.Update(Convert(item, f));
        }
        if (item is ItemContainer container)
        {
            foreach (var child in container.Children)
                Save(items, child);
        }
    }

The unittest creates 100.000 items and saves them to a new file, so FindById never finds something and calls Insert for each item. Most of the time is spent in FindById and Insert.

I don't know if the whole performance degradation is related to the missing transactions, but it seems to be the biggest part. The bulk object which you mentioned above would easy our pain. Currently we refrain from updating to 4.0.0.

[mbdavid]

Hi @MichaelRumpler,

In this case, without transaction, each insert/update needs write on disk. Thats why it´s take much more time. But in you case add all modified items into a single List and run an Upsert that will insert/update items at same time.

using (var liteDatabase = new LiteDatabase(connectionString))
{
    var items = liteDatabase.GetCollection("items");
    var bag = new List<ItemBase>();

    Navigate(bag, database.root)

    items.Upsert(bag.Select(x => Convert(x, f)));

    bag.ForEach(x => x.IsModified = false);
}

private void Navigate(List<ItemBase> bag, ItemBase item)
{
    if (item.IsModified)
    {
        bag.Add(item);
    }

    if (item is ItemContainer container)
    {
        foreach (var child in container.Children)
        {
            Navigate(bag, child);
        }
    }    
}

I'm still studying an way to create batch operations to run as a transaction (like EF do with Context).

[MichaelRumpler]

Yes, Upsert is much better! The test takes 11 seconds now which is ok for such a large amount of changes.

Thanks for your fast response and the fast solution!

[mbdavid]

Hi guys, please, take a look here: #791

[lbnascimento]

Hi! With the objective of organizing our issues, we are closing old unsolved issues. Please check the latest version of LiteDB and open a new issue if your problem/question/suggestion still applies. Thanks!