Multiple access to a single SQLite database file via System.Data.SQLite and c#

Question

As I can read from SQLite FAQ it supports multiple processes reading (SELECT) and only one process writing (INSERT, UPDATE, DELETE) database at any moment in time:

SQLite uses reader/writer locks to control access to the database. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business

I'm using System.Data.SQLite adapter via c#.

Could someone expalin me plz, how exactly this process is going on?

Will this process work automatically and writing SQLiteCommand will simply wait if there is another writing SQLiteCommand already executing over the same database?

Or maybe it will throw an exception? What kind of it?

Sorry but I found no information about this mechanics :)

Thank you.

UPDATE:

I've found post saying that exception will be raised with a specific errorcode

Is that statement correct?

Solution

I've investigated it by myself:

I created a sample SQLite database c:\123.db with one table Categories containing two fields: ID (uniqueidentifier) and Name (nvarchar).

I then wrote some multi-thread code to emulate multiple write access to the database (don't forget to add a System.Data.SQLite reference to your project if you use this code):

using System;
using System.Data.SQLite;
using System.Threading.Tasks;

namespace SQLiteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var tasks = new Task[100];

            for (int i = 0; i < 100; i++)
            {
                tasks[i] = new Task(new Program().WriteToDB);
                tasks[i].Start();
            }

            foreach (var task in tasks)
                task.Wait();
        }

        public void WriteToDB()
        {
            try
            {
                using (SQLiteConnection myconnection = new SQLiteConnection(@"Data Source=c:\123.db"))
                {
                    myconnection.Open();
                    using (SQLiteTransaction mytransaction = myconnection.BeginTransaction())
                    {
                        using (SQLiteCommand mycommand = new SQLiteCommand(myconnection))
                        {
                            Guid id = Guid.NewGuid();

                            mycommand.CommandText = "INSERT INTO Categories(ID, Name) VALUES ('" + id.ToString() + "', '111')";
                            mycommand.ExecuteNonQuery();

                            mycommand.CommandText = "UPDATE Categories SET Name='222' WHERE ID='" + id.ToString() + "'";
                            mycommand.ExecuteNonQuery();

                            mycommand.CommandText = "DELETE FROM Categories WHERE ID='" + id.ToString() + "'";
                            mycommand.ExecuteNonQuery();
                        }
                        mytransaction.Commit();
                    }
                }
            }
            catch (SQLiteException ex)
            {
                if (ex.ReturnCode == SQLiteErrorCode.Busy)
                    Console.WriteLine("Database is locked by another process!");
            }
        }
    }
}

The result on my Core2Duo E7500 is that Exception is never raised!

Looks like SQLite is optimised enough for my needs (locking/unlocking is really fast and normally only takes a few milliseconds as SQLite FAQ tells us) - Great!

Note that there is no need to retrieve an integer ErrorCode for an SQLiteException - you can use a special enum ReturnCode field instead. All codes are described here.

Hope this information will help somebody.

Comments

[pmont]

Your code proves that multiple threads within a single process can seamlessly access the SQLite database. Were you able to test whether multiple different processes can access the same database?

[bairog]

Well, first that comes to my mind is compiling that code in Test1.exe, than add Process.Start("Test1.exe") at the first line of Main and recompile it to Test2.exe. Seems to be working but I've never tested it...

[pmont]

Good idea! I just tested using that approach and it works. Multi-process access works just fine with SQLite.

[James Eby]

Just want to supplement this. I am writing an NLog / SignalR based "Guaranteed Delivery" logging service that is using System.Data.SQLite to write to a local database for a background thread to send that data on to LogStash and I had 40 threads sending log messages to a local SignalR hub. It most definitely was throwing SQLite error (5): database is locked exceptions when that single process was processing messages from other threads of other processes.

[James Eby]

CORRECTION I thought the SQLite error (5): database is locked was my windows service running in console mode catching an error and displaying it... BUT this this appears to be SQLite internal code because I am not having any errors and I am monitoring the number of inserts I should be inserting in to the database and I am not losing any inserts... So BRAVO SQLITE team!

[James Eby]

So for my test, 6 processes, 10 threads each, sending 100 log messages each to a central logging SignaR Hub. All 6000 log records were written with no special code compensating for DB locks.

[bairog]

Sounds really great!

[m3nda]

@JamesEby SQlite uses internal journaling, so when something fails because a block status or other, it retries untill done. So, is not internal conding, and of course your inserts are not being lost. In fact, if you see the message, an error happend, and a retry happend later. This kind of "locking" error is common when you use threads to inserts. Aside of that, and if you have a HUGE read from threads, is to use WAL journal model, so writes and reads are in separate threads, so writes from threads don't affect possible thousand concurrent reads.

[Banana Cake]

I tied this and had database is locked error, but I solved it by using Async versions of this methods and it works perfectly. For SELECT query is not necessary to use Async method.