C#•2mo ago

Threading and Microsoft.Data.Sqlite

I'm using multithreading. Each thread will make a query to a SQL Database by using a static class. They should all use the same SqliteConnection connection object that is a field of static class SqlDatabase. I feel like there is only one "opening" of the connection, and therefore there shouldn't be any problems of handling "many users" from SQLite perspective. However, I still get an error:

Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Sqlite.SqliteConnection.RemoveCommand(SqliteCommand command)
   at Microsoft.Data.Sqlite.SqliteCommand.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()

Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Sqlite.SqliteConnection.RemoveCommand(SqliteCommand command)
   at Microsoft.Data.Sqlite.SqliteCommand.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()

It looks like it tries to dispose several times the same object, and gets me a bit confused as to what is happening. It's probably because all my threads are trying to use the same object (I explain after the MRE why I think that's the case)?

9 Replies

JasonnnOP•2mo ago

Here is a Minimal Reproducible Example:

using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace SQLiteMultithreadingExample
{
    static class SqlDatabase
    {
        private static readonly Lazy<SqliteConnection> LazyConnection = new Lazy<SqliteConnection>(
            () =>
            {
                var connection = new SqliteConnection("Data Source=Core.db;Mode=ReadOnly");
                connection.Open();
                return connection;
            }
        );

        private static SqliteConnection Connection => LazyConnection.Value;
        
        public static void QueryDatabase()
        {
            using (var command = Connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM KITS";
                
                using (var reader = command.ExecuteReader())
                {
                    int rowCount = 0;

                    while (reader.Read())
                    {
                        rowCount++;
                    }

                    Console.WriteLine($"Row count in KITS table is {rowCount}");
                }
            }
        }
    }
    
    class Program
    {

        static async Task Main(string[] args)
        {
            // Number of threads/tasks
            int numberOfThreads = 100;
            Task[] tasks = new Task[numberOfThreads];

            // Start each task to open the database and query KITS table
            for (int i = 0; i < numberOfThreads; i++)
            {
                tasks[i] = Task.Run(() => SqlDatabase.QueryDatabase());
            }

            // Wait for all tasks to complete
            await Task.WhenAll(tasks);

            Console.WriteLine("All threads have completed their work.");
        }
    }
}

using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace SQLiteMultithreadingExample
{
    static class SqlDatabase
    {
        private static readonly Lazy<SqliteConnection> LazyConnection = new Lazy<SqliteConnection>(
            () =>
            {
                var connection = new SqliteConnection("Data Source=Core.db;Mode=ReadOnly");
                connection.Open();
                return connection;
            }
        );

        private static SqliteConnection Connection => LazyConnection.Value;
        
        public static void QueryDatabase()
        {
            using (var command = Connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM KITS";
                
                using (var reader = command.ExecuteReader())
                {
                    int rowCount = 0;

                    while (reader.Read())
                    {
                        rowCount++;
                    }

                    Console.WriteLine($"Row count in KITS table is {rowCount}");
                }
            }
        }
    }
    
    class Program
    {

        static async Task Main(string[] args)
        {
            // Number of threads/tasks
            int numberOfThreads = 100;
            Task[] tasks = new Task[numberOfThreads];

            // Start each task to open the database and query KITS table
            for (int i = 0; i < numberOfThreads; i++)
            {
                tasks[i] = Task.Run(() => SqlDatabase.QueryDatabase());
            }

            // Wait for all tasks to complete
            await Task.WhenAll(tasks);

            Console.WriteLine("All threads have completed their work.");
        }
    }
}

If I do:

var connection = new SqliteConnection("Data Source=Core.db;Mode=ReadOnly");

var connection = new SqliteConnection("Data Source=Core.db;Mode=ReadOnly");

as a local variable in public static void QueryDatabase() then I don't have any problems anymore (but then I'm opening 100 different connections!) Which is why I'm pretty much convinced the problem comes from the fact that all my threads are using the same object. So I'm a bit confused as to what I should be doing, what's good practice, and why I have an error in the first place

HtmlCompiler•2mo ago

can you print thread id inside QueryDatabase? i'm not actually sure this is using that many threads

JasonnnOP•2mo ago

It’s only using around 15 threads for some reason (I would assume the query is too fast in my MRE, but since I'm able to reproduce the problem I have that's good enough for me)

Thread 10 read 17 rows.
Thread 6 read 17 rows.
Thread 16 read 17 rows.
Thread 10 read 17 rows.
Thread 11 read 17 rows.
Thread 6 read 17 rows.
Thread 12 read 17 rows.
Thread 9 read 17 rows.
Thread 14 read 17 rows.
Thread 15 read 17 rows.
Thread 18 read 17 rows.
Thread 16 read 17 rows.
Thread 7 read 17 rows.
Thread 8 read 17 rows.
Thread 13 read 17 rows.
Thread 18 read 17 rows.
Thread 17 read 17 rows.
Thread 20 read 17 rows.
Thread 19 read 17 rows.
Thread 12 read 17 rows.
Thread 14 read 17 rows.
Thread 11 read 17 rows.
Thread 10 read 17 rows.
Thread 6 read 17 rows.
Thread 9 read 17 rows.
Thread 15 read 17 rows.
Thread 16 read 17 rows.
Thread 8 read 17 rows.
Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Sqlite.SqliteConnection.RemoveCommand(SqliteCommand command)
   at Microsoft.Data.Sqlite.SqliteCommand.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at SQLiteMultithreadingExample.SqlDatabase.QueryDatabase() in

Thread 10 read 17 rows.
Thread 6 read 17 rows.
Thread 16 read 17 rows.
Thread 10 read 17 rows.
Thread 11 read 17 rows.
Thread 6 read 17 rows.
Thread 12 read 17 rows.
Thread 9 read 17 rows.
Thread 14 read 17 rows.
Thread 15 read 17 rows.
Thread 18 read 17 rows.
Thread 16 read 17 rows.
Thread 7 read 17 rows.
Thread 8 read 17 rows.
Thread 13 read 17 rows.
Thread 18 read 17 rows.
Thread 17 read 17 rows.
Thread 20 read 17 rows.
Thread 19 read 17 rows.
Thread 12 read 17 rows.
Thread 14 read 17 rows.
Thread 11 read 17 rows.
Thread 10 read 17 rows.
Thread 6 read 17 rows.
Thread 9 read 17 rows.
Thread 15 read 17 rows.
Thread 16 read 17 rows.
Thread 8 read 17 rows.
Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
   at Microsoft.Data.Sqlite.SqliteConnection.RemoveCommand(SqliteCommand command)
   at Microsoft.Data.Sqlite.SqliteCommand.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at SQLiteMultithreadingExample.SqlDatabase.QueryDatabase() in

HtmlCompiler•2mo ago

Lazy<T> is no thread safe, it has a parameter in the constructor for that either that or you put a synchronization primitive in the lazy method but even before that i would in general reconsider using lazy or in general having db in multiple threads like, do you really need this, are you sure

JasonnnOP•2mo ago

Ah I see Edit: but it says « By default, Lazy<T> objects are thread-safe. That is, if the constructor does not specify the kind of thread safety, the Lazy<T> objects it creates are thread-safe. In multi-threaded scenarios, the first thread to access the Value property of a thread-safe Lazy<T> object initializes it for all subsequent accesses on all threads, and all threads share the same data. » in the documentation Well it was either I spawn one connection per thread Which I thought was bad Or I have one connection object that all threads use But then I need to have some sort of « locking » mechanism?

HtmlCompiler•2mo ago

my fault, i remembered wrong from a discussion here not much time ago at this point the ball falls in sqlite's court

JasonnnOP•2mo ago

Umm I’m not sure, isn’t it a c# problem Because if I only have one connection object SQLite only sees « one user » right? Like the error looks like a GC problem

HtmlCompiler•2mo ago

i would wager GC is pretty stable at this point if SqliteConnection is in a microsoft package are you sure it's not already managing a connection pool for you? there should already be doc somewhere to do this

JasonnnOP•2mo ago

The reason why I thought sqlite was handling it well was that when each thread uses its own connection object, it doesn’t crash. I’ll look for the documentation of SqliteConnection

Gaming

Programming

Threading and Microsoft.Data.Sqlite