Multi-threaded C# test for SQLite fails with 'database table is locked'

(1) By cmgain on 2022-03-30 21:06:07 [source]

Hi

Using C# SQLite in-memory database, I have a standalone unit test failing on me. I create & fill a table, launch 20 threads just reading from it and then start an update loop.

Most of the time when running the test (but not always), it fails on ExecuteNonQuery() when updating with SqliteException : SQLite Error 6: 'database table is locked: TableName'.

My expectation is that the SELECTs never interfere with the strictly sequential UPDATEs and I wonder how to fix the below.

Thanks


    public class SqliteTest
    {
        private volatile bool _stop = false;

        [Test]
        public void Test()
        {
            var dataSource = "Data Source=TEST;Mode=Memory;Cache=Shared";

            using var connection = new SqliteConnection(dataSource);
            connection.Open();

            var command = connection.CreateCommand();
            command.CommandText = "CREATE TABLE TableName ( key TEXT NOT NULL PRIMARY KEY, id TEXT, name TEXT ); " +
                                  "CREATE INDEX TableName_Id on TableName ( id ); ";
            command.ExecuteScalar();

            int noOfRows = 30_000;

            for (int i = 0; i < noOfRows; i++)
            {
                command = connection.CreateCommand();
                command.CommandText = $"INSERT INTO TableName ( key, id, name) VALUES (\"key{i}\", \"id{i}\", \"name{i}\")";
                command.ExecuteScalar();
            }

            var pragmaCmd = connection.CreateCommand();
            pragmaCmd.CommandText = $"PRAGMA query_only=true"; // after this executing SELECTs only
            pragmaCmd.ExecuteNonQuery();

            var noOfThreads = 20;

            Random rnd = new Random();

            var queryThreads = new Thread[noOfThreads];
            for (int i = 0; i < noOfThreads; i++)
            {
                var j = i;
                queryThreads[i] = new(_ =>
                {
                    while (!_stop)
                    {
                        var n = $"{j * 1000 + rnd.Next(0, 1000)}";
                        SqliteCommand selectCommand = connection.CreateCommand();
                        selectCommand.CommandText = $"SELECT key, id FROM TableName where key = \"key{n}\"";
                        AssertSqliteDataReader(selectCommand, $"key{n}", $"id{n}");
                    }
                });
            }

            for (int i = 0; i < noOfThreads; i++)
            {
                queryThreads[i].Start();
            }

            var noOfUpdates = 1_000;

            using var updateConnection = new SqliteConnection(dataSource);
            updateConnection.Open();

            pragmaCmd = updateConnection.CreateCommand();
            pragmaCmd.CommandText = $"PRAGMA busy_timeout=0"; // no busy timeout - not sure if this is working
            pragmaCmd.ExecuteNonQuery();

            for (int i = 0; i < noOfUpdates; i++)
            {
                using (var transaction = updateConnection.BeginTransaction(IsolationLevel.Serializable, false))
                {
                    try
                    {
                        var k = rnd.Next(0, noOfRows);
                        var l = (char)('A' + rnd.Next(0, 26));
                        SqliteCommand cmd = updateConnection.CreateCommand();
                        cmd.Transaction = transaction;
                        cmd.CommandText = $"UPDATE TableName set name = \"name{l}\" WHERE key = \"key{k}\"";
                        Assert.AreEqual(1, cmd.ExecuteNonQuery());
                        transaction.Commit();
                    }
                    catch (SqliteException)
                    {
                        _stop = true;
                        throw;
                    }
                }
            }

            _stop = true;

            for (int i = 0; i < noOfThreads; i++)
            {
                queryThreads[i].Join();
            }

            Console.WriteLine("Done");
        }

        private static void AssertSqliteDataReader(SqliteCommand sqliteCommand, params string[] fields)
        {
            using SqliteDataReader reader = sqliteCommand.ExecuteReader();
            if (reader.Read())
            {
                if (reader.FieldCount != fields.Length)
                {
                    Assert.Fail("Number of result fields not matching");
                }

                for (var i = 0; i < reader.FieldCount; i++)
                {
                    Assert.AreEqual(fields[i], reader.IsDBNull(i) ? null : reader.GetString(i));
                }
            }
            else
            {
                Assert.Fail("No result");
            }
        }
    }

(2) By Keith Medcalf (kmedcalf) on 2022-03-30 21:48:22 in reply to 1 [link] [source]

Working as designed. Your implementation is insufficient in performing as you desire. You need to revisit your implementation.

(3.1) By Keith Medcalf (kmedcalf) on 2022-03-30 21:54:57 edited from 3.0 in reply to 1 [link] [source]

Hint: Have you set the busy_timeout to a useful non-default value? I might suggest trying 1000.
Hint: Why are you using shared cache?

(4.1) By cmgain on 2022-03-31 09:51:57 edited from 4.0 in reply to 3.1 [link] [source]

Thank you for your reply!

I'm happy to make changes but so far all my attempts have been futile :-)

In my actual code, the process starts up two components (running in separate threads) both creating their own connection to the same in-memory database.

Component/thread A sets up the database schema and then just runs UPDATEs (no further schema changes).

Component/thread B is frequently running SELECTs (and nothing else).

Re: busy_timeout

In my previous tests, I tried busy_timeout to 0 (I tried 1000 instead of the default 30000 as well) but it didn't seem to make any difference.

Re: Shared Cache

Changing my test, I tried it without "Cache=Shared" but I then get errors because the table doesn't exists in on the connection that didn't create it - feels like the connection is for a private in-memory database even though it was given a specific name.

This Microsoft .NET page seems pretty specific that my connection string ("Data Source=TEST;Mode=Memory;Cache=Shared") is correct. It even has a link to a sample on GitHub This looks quite similar to my test apart from the fact that there are no concurrent SELECTs/UPDATEs.

I'm currently using SQLite version 3.35.5 released 19 Apr 2021 by way of NuGet C# dependency from Microsoft.Data.Sqlite 6.0.2

Thanks Christian

(5) By cmgain on 2022-03-31 14:36:26 in reply to 3.1 [link] [source]

I have re-created my test in Java and get the same result: the UPDATEs run into the same "database table is locked: TableName"

Additionally, without cache=shared my in-memory connections are private (table can't be found from other connections)

For the record: I'm using 'org.xerial:sqlite-jdbc:3.36.0.3' and connection string "jdbc:sqlite:file:test?mode=memory&cache=shared"

(6.1) By cmgain on 2022-03-31 18:08:44 edited from 6.0 in reply to 3.1 [link] [source]

With all the SQLite options and documentation about shared caches, multi-thread safety and concurrency, I begin to wonder if it is the right tools for me...

I don't need (but could live with) file persistence. Forget about performance and the amount of concurrency, how would I set up a process with multiple threads - only one of them a writer (admittedly a big one: update transactions lasting up to 10 seconds and changing 50% of rows), and a few readers.

I don't want to ever have my writes to fail but are happy for the readers to have to wait 10 seconds.

How would one go about this? Maybe instead of an in-memory database, use real file in /dev/shm - every thread with its own connection?

Or do I just have to throttle my readers and hope for the best?

Wondering...

(7.1) By Keith Medcalf (kmedcalf) on 2022-04-03 16:43:13 edited from 7.0 in reply to 6.0 [link] [source]

Use a memdb. Provided that the filename commences with a single / you can open the same memdb on multiple connections in the same process.

:memory: (like '') is a database local to the connection which creates it -- and although you can use shared cache if you wish to allow the same database to be opened on multiple pseudo-connections, using shared cache changes the behaviour significantly.

The simplest way to use a memdb is to make sure that you have enabled URL filenames and use the URL file:/name?vfs=memdb which will permit you to "connect" to the same memdb multiple times from the same process and have it act like a real (shared file) db.

(8) By cmgain on 2022-03-31 19:13:01 in reply to 7.0 [link] [source]

Interesting!

For my Java test case - without code changes - switching from "jdbc:sqlite:file:test?mode=memory&cache=shared" to "jdbc:sqlite:file:/test?vfs=memdb" fixed all the issues I have seen. I run the test for quite a while...

For C# though, switching the connection string from "Data Source=TEST;Mode=Memory;Cache=Shared" to "Data Source=file:/TEST?vfs=memdb" gave me an error saying "SQLite Error 1: 'no such vfs: memdb'"

I don't supply my own SQLite libraries but rely on them being pulled in as dependencies. Could it be that I get one for Java (SQLite 3.36.0) with more stuff enabled at compile time compared to .NET/C# (SQLite 3.35.5) ? I could try to just upgrade to something later.

Thanks for your help! Christian

(9) By cmgain on 2022-03-31 19:38:07 in reply to 7.0 [link] [source]

The very latest version of Microsoft.Data.Sqlite (7.0.0-preview.2.22153.1) still seems to be on SQLite 3.35.5 :-(

(10) By cmgain on 2022-03-31 20:04:53 in reply to 7.0 [link] [source]

And I'm finding more information that raise further questions: I'm currently using Microsoft.Data.Sqlite maintained by MS - should I actually be looking into System.Data.SQLite maintained by the SQLite team?

Thanks again!

(11) By cmgain on 2022-04-01 14:23:18 in reply to 10 [link] [source]

Update: I struggled a little with the connection string for System.Data.SQLite but eventually using "FullUri=file:/TEST?vfs=memdb" (no "Data Source" component) worked.

This worked too: "FullUri=file:/TEST;VfsName=memdb"

I guess, I will switch from Microsoft to System.Data.SQLite in my project.

Thank you for your pointers - "vfs=memdb" should be added to the documentation wherever in-memory databases are discussed such that if you are using SQLite in a multi-threaded scenario, don't use ":memory:" but "vfs=memdb"

Cheers!

(12) By cmgain on 2022-04-11 16:04:31 in reply to 10 [link] [source]

Update:

we moved to System.Data.SQLite - mostly just renames in the source code, apart of some stuff related to SQL types and some inconsistency when disposing objects - System.Data.SQLite seems to do more thorough checking...

And our transactions run quicker too!