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?

Answer 1

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.