Litedb: System.UnauthorizedAccessException when inserting

[UweKeim]

Today I found LiteDB and took some _very_ basic tests:

• I've used the Basic example from www.litedb.org
• Put it into a 1000 loop.

What happens next will shock you 😉:

After several iterations (sometimes 28, sometimes 101, sometimes ...) I get an exception:

System.UnauthorizedAccessException
Access to MyData-journal.db was denied.

This is the _complete_ source code of my .NET 4.7 console application:

```c#
namespace TestNoSQL
{
using LiteDB;
using System;
using System.Diagnostics;
using System.IO;
using System.Reflection;

// Basic example
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string[] Phones { get; set; }
    public bool IsActive { get; set; }
}

internal static class Program
{
    private static readonly string _path =
        Path.GetDirectoryName(Assembly.GetEntryAssembly().Location).TrimEnd('\\') + 
        @"\MyData.db";

    private static void Main(string[] args)
    {
        const int count = 1000;

        var sw = new Stopwatch();
        sw.Start();
        for (var i = 0; i < count; ++i)
        {
            one();
        }
        sw.Stop();

        Console.WriteLine($@"{count} times '{nameof(one)}' took. {sw.Elapsed}.");
    }

    private static void one()
    {
        // Open database (or create if not exits)
        using (var db = new LiteDatabase(_path))
        {
            // Get customer collection
            var customers = db.GetCollection<Customer>("customers");

            // Create your new customer instance
            var customer = new Customer
            {
                Name = "John Doe",
                Phones = new string[] { "8000-0000", "9000-0000" },
                IsActive = true
            };

            // Insert new customer document (Id will be auto-incremented)
            customers.Insert(customer);

            // Update a document inside a collection
            customer.Name = "Joana Doe";

            customers.Update(customer);
        }
    }
}

}
```

My question:

Is this a design limitation or am I doing something significantly wrong?

[mbdavid]

Hi @UweKeim, try use v4 beta from Nuget/Master branch. You are using v3.x that I found some problems that I'm fixing for next version

[UweKeim]

It is working as expected with v4. Thanks a lot, David!

Even cross-threading seems to work perfectly:

Multiple connections              : 1000 times 'one' took. 00:00:05.4864547.
One connection                    : 1000 times 'one' took. 00:00:06.6925723.
One connection multi-threaded     : 1000 times 'one' took. 00:00:08.1019311.
Multiple connection multi-threaded: 1000 times 'one' took. 00:00:14.1637235.

FWIW, here is my updated full code:

```c#
namespace TestNoSQL
{
using LiteDB;
using System;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Threading;

// Basic example
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string[] Phones { get; set; }
    public bool IsActive { get; set; }
}

internal static class Program
{
    private static readonly string _path =
        Path.GetDirectoryName(Assembly.GetEntryAssembly().Location).TrimEnd('\\') + @"\MyData.db";

    private static void Main(string[] args)
    {
        const int count = 1000;

        var sw = new Stopwatch();
        sw.Start();
        for (var i = 0; i < count; ++i)
        {
            one();
        }
        sw.Stop();

        Console.WriteLine($@"Multiple connections              : {count} times '{nameof(one)}' took. {sw.Elapsed}.");

        // --

        sw.Start();
        // Open database (or create if not exits)
        using (var db = new LiteDatabase(_path))
        {
            for (var i = 0; i < count; ++i)
            {
                coreOne(db);
            }
        }
        sw.Stop();

        Console.WriteLine($@"One connection                    : {count} times '{nameof(one)}' took. {sw.Elapsed}.");

        // --

        long p = 0;

        sw.Start();
        // Open database (or create if not exits)
        using (var db = new LiteDatabase(_path))
        {
            for (var i = 0; i < count; ++i)
            {
                var db1 = db;
                ThreadPool.QueueUserWorkItem(delegate
                {
                    coreOne(db1);
                    Interlocked.Increment(ref p);
                });
            }

            while (Interlocked.Read(ref p) < count)
            {
                Thread.Sleep(0);
            }
        }

        sw.Stop();

        Console.WriteLine($@"One connection multi-threaded     : {count} times '{nameof(one)}' took. {sw.Elapsed}.");

        // --

        p = 0;

        sw.Start();
        for (var i = 0; i < count; ++i)
        {
            ThreadPool.QueueUserWorkItem(delegate
            {
                one();
                Interlocked.Increment(ref p);
            });
        }

        while (Interlocked.Read(ref p) < count)
        {
            Thread.Sleep(0);
        }

        sw.Stop();

        Console.WriteLine($@"Multiple connection multi-threaded: {count} times '{nameof(one)}' took. {sw.Elapsed}.");
    }

    private static void one()
    {
        // Open database (or create if not exits)
        using (var db = new LiteDatabase(_path))
        {
            coreOne(db);
        }
    }

    private static void coreOne(LiteDatabase db)
    {
        // Get customer collection
        var customers = db.GetCollection<Customer>("customers");

        // Create your new customer instance
        var customer = new Customer
        {
            Name = "John Doe",
            Phones = new string[] { "8000-0000", "9000-0000" },
            IsActive = true
        };

        // Insert new customer document (Id will be auto-incremented)
        customers.Insert(customer);

        // Update a document inside a collection
        customer.Name = "Joana Doe";

        customers.Update(customer);

        // Index document using a document property
        customers.EnsureIndex(x => x.Name);

        // Use Linq to query documents
        var results = customers.Find(x => x.Name.StartsWith("Jo"));
    }
}

}
```

[mbdavid]

The next question will be about your example in concurrency mode. v3 has some problems with some multi-thread/task but it's fixed in v4 (so far I have not had any problems of this type reported in this new version)

[UweKeim]

Thanks again, David. I'll stick to V4 with my further tests.

(I'm trying to compare similar operations with ADO.NET with MS Access now to get a feeling about speed)

---

Off-topic question: Is your first name "David" or "Mauricio"? Probably I did it wrong above; sorry about that!

[UweKeim]

One last addition, my comparison between MS Access and LiteDB:

Access             of 1000 WRITE iterations took 00:00:38.9010116.
LiteDB             of 1000 WRITE iterations took 00:00:02.0247147.
LiteDB (in-memory) of 1000 WRITE iterations took 00:00:00.1703879.

Access             of 1000 READ  iterations took 00:00:47.4544505.
LiteDB             of 1000 READ  iterations took 00:00:00.0133699.
LiteDB (in-memory) of 1000 READ  iterations took 00:00:00.0122957.

This is based on the Basic example with the Customer class.

I've also added a test based on this ticket in order to compare with an in-memory version of the LiteDB database file.

[mbdavid]

Huge difference between Access and LiteDB 😄

LiteDB is 100% managed .NET code, runs in-process. Read access are optimized to re-use cached block pages, so disk access are reduced too.